Thread: Average over different time ranges

Hi

I have some data, consisting of two columns. The 1st column consists of time values in the form hh:mm:ss, and the 2nd column consists of a pressure reading, taken every couple of seconds.
I wish to find the average pressure from the start time, to a 2nd time, then the average pressure from this 2nd time to a 3rd time, then the the av. pres. from this 3rd time to a 4th time, and so on. The time i want to take the average over varies, sometimes 3 minutes (i.e. ~180 pressure readings), sometimes 4, sometimes 5 minutes. I have to take the average over different ranges dozens of times - its very time consuming! If anyone has an idea how to create a macro to this for me i would be very grateful.
Attached is a zip file of a small part of my data. The averages to be calculated are on the right.
As you can see the averages are calculated over different time periods (10 mins, 3mins, 5mins etc).
Thanks for any help

NCherry

You dont need a Macro for this, it can be done with formulas.

first you need to convert your time column to Excel Time format and remove the decimals for the seconds. This is done with this formula:

=TIMEVALUE(LEFT(A2,FIND(".",A2)-1))

Then you use SUMPRODUCT to count the number of readings, and the sum of the readings. The N() formula is used to convert an array of TRUE and FALSE to an array of 1 and 0.

Use Tools -> Formula Auditing -> Evaluate Formula to see how this (and any other) formula works.

See attachment

Wow that clever thanks very much!
I could of never of done that!

Nasal

You need to be careful that you use all data points. By using > and < as in Bjornar's example you'll miss the readings which are exactly at the extremes of your ranges, e.g. 13:30 or 13:40.

I'd suggest that you average for times >= to 13:30 but < 13:40 then you'll include all your readings without double counting.

You could use a single formula in H3 copied down based on your original data, i.e. in H3

=AVERAGE(IF(A$2:A$2000+0>=F3,IF(A$2:A$2000+0<G3,B$2:B$2000)))

This is an array formula which must be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar