Need to calculate average value every 15 mins

Hi guys/gals!

See the attached sample data.

I am needing to have a formula that looks in column A. The formula should give an average of the values in column E, every 15 miins, starting at 22:00 and ending at 00:15. So I should get 9 values in total.

I cannot figure out how to do this as there are multiple values for each minute, rather than 1 value per minute.

I think your dates in column A are in the format of mm/dd/yyyy, as you go from 12/06/2019 to 12/07/2019, even though they are formatted to show dd/mm/yyyy. It doesn't really matter, as we can extract the time element from those date/times, but I thought I'd point it out to you.

Pete

As we are only concerned with time, it would help to split those times away from the dates, so put this formula in cell F2:

=A2-INT(A2)

Format that cell as a time (hh:mm:ss), then copy the formula down to the bottom of your data.

Then you can build up a summary table, so enter your start time (20:00:00) in G2 and put this formula in G3:

=G2+TIME(0,15,0)

Format those two cells as a time, then copy G3 down to G11. You will need to change the formula in G10 to this:

=G9+TIME(0,15,0)-1

to take account of passing midnight. Then you can use this formula in H2:

=AVERAGEIFS(E:E,F:F,">="&G2,F:F,"<"&G2+TIME(0,15,0))

then copy this down to H11.

Hope this helps.

Pete

Did you forget to attach a file?

Pete

read the yellow bars at the head of the page, not everyone (including me) are prepared to use dropbox.

Hi,

OK, I have cut down the sheet in size. So its the cells in red that don't seem to be calaculating as expected.

Its the rounding of the values and floating point calculation

try
=COUNTIF(B:B,MROUND(H2,"00:15")) to force every 15 minutes exactly

similar adjustments to all your other formula, but I am sure you can do that

Hi,

Strange, it has amended column J, but I am still getting zeros in the averages. See attached.

I found a workaround. Thanks for your help.

Thank you! This was a great help