Need help with calculating averages over dynamic ranges

[Needhelpp1]

Hello,

I'm trying to get a formula right which helps me to return averages of specific vertical ranges from differing length in my Excel sheet.

In column A, time stamps of the measurements are available, given as hh:mm:ss. In the second column, the measurements are listed. I want to calculate the avergae of the measurements (column B) from time windows 00:00:01 to 00:00:20, form 00:00:21 to 00:00:40, from 00:00:41 to 00:01:00 and so on. Within each time period of 20 seconds, the number of measurements differ. I've been tryign to get this right using offset and if, then formulas, but I can't get them to work.

Example data are enclosed. Thanks!

[Pepe Le Mokko]

Hi
with a small helper column you can create a Pivot Tabl like the attched

Define your range as a Table to make the dynamic

[Glenn Kennedy]

Creating a time range can sometimes be problematic!! as you have seen.... It's all down to floating point aritmentic... rounding errors.

With 00:00:01 in E2 and 00:00:20 in F2, use these to create the time series in E3:=E2+$F$2 and in F3: $F$2+F2

Now a nice simple averageifs will work in G2:

=IFERROR(AVERAGEIFS(B:B,A:A,">="&E2,A:A,"<="&F2),"")

You may need ; instead of , - see the sheet for context.

[Needhelpp1]

Thanks to the both of you! This was very helpful.

Glenn, your solution is near to perfect, although if you calculate averages by hand for the specified windows, the results are slightly different than the results from the formula you have provided.
I've been trying to correct this, but the problem still exists; I think it might be due to measurements made at the cut-off values of time windows. Is there a way to work around this issue?

Thanks again!

[bebo021999]

With Glenn's, make a slight amenmend:
G2: leave as it was.
In G3:

Please Login or Register  to view this content.

Drag down

[Needhelpp1]

Thank you for your help, but this does not seem to solve the issue. See the updated test data enclosed. There is still a mismatch between averag and "correct" average...

[Glenn Kennedy]

Time starts at zero, not at one second. So, I changed E2 to zero, as you are wanting average of 20 second blocks from t=0. That has no material effect in the "discrepancies" as there was none in the first block. It just makes it correct!!

You are averaging 20 second blocks. >0-20. >20-40. >40-60, etc. The difference is in the milliseconds, which you could not see when data were presented as hh:mm:ss.

See sheet.

[Needhelpp1]

Hi Glenn,

Thank again, also for the clear visual explanation with the time stamps showed with their decimals. This explains the "discrepancies", that were no discrepancies after all.

Cheers!

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.