Formula to calculate Average between times

[Jet Fusion]

Hi

How would I insert a formula to calculate the average of a range of data between given times eg between the hours 06:00 - 09:00 in the morning it should give an average of the data in B9:B52 and put it in C6 and so on. If C is done then it's a matter of copying and changing the times for the rest of the columns.

Thanks in advance (file attached)

 

[Peter Bartholomew]

Basically you convert the time column to values rather than text and then use SUMIFS, MAXIFS etc.

= AVERAGEIFS(
    total_kW,
    time, ">"&TIME(6,0,0),
    time, "<="&TIME(9,0,0)
  )

 

[p45cal]

See attached.
Column A times need to be converted from strings to time values (numbers): Select the values in column A, go to Text to Columns in the Data section of the Data tab of the ribbon and in the third step do this:



and click Finish. Then you can use the formulae in row 6 of the attached.
I've assumed that the timestamps in column A are the beginning of each 15 minute interval. If that's not the case then you need to change the red portions of all the formulae, from:
=AVERAGEIFS($B$9:$B$52,$A$9:$A$52,">=" & TIME(6,0,0),$A$9:$A$52,"<"&TIME(9,0,0))
to:
=AVERAGEIFS($B$9:$B$52,$A$9:$A$52,">" & TIME(6,0,0),$A$9:$A$52,"<="&TIME(9,0,0))

 

[Jet Fusion]

Thank you @Peter Bartholomew & @p45cal much appreciated

 

[Jet Fusion]

is there a way that we can adapt the formulas to ignore 0's and blank cells as I see on the lowest load it give me a negative value which I am assuming is due to the o's in some of the cells?

Thank you

 

[Peter Bartholomew]

You can include additional tests within the formula but I doubt that will account for the difference,

= MINIFS(
      Total_kW,
      Total_kW, "<>0",
      Time,     ">"&TIME(6,0,0),
      Time,     "<="&TIME(9,0,0)
  )