Finding an average for a specified date range

**dee101** · 05-07-2012, 01:59 AM

Hi everyone

I'm trying to work out the average for a range of values between specified dates and times. The data set contains values for a financial year, with a new value each half hour.

In my example I'm trying to find out the average of values between '01/04/2007 0:30' and '01/07/2001 0:00'. I need to do the same thing for each quarter (i.e., from 0:30 on the first day of every quarter to 0:00 of the first day of the next quarter)

The formula I'm trying to use is an array that goes like:
{=AVERAGE(IF([full range of dates]>=(start day of quarter+0:30),IF([full range of dates]<=(start day of next quarter+0:00),[range of values to average])))}

See the attached sheet for the formula in context.

Now the problem with this formula is that it almost works. It's about .02 or so out for many quarters across different years that I'm aggregating. Can anyone tell me what I need to do to make this work?

thanks
D

**vlady** · 05-07-2012, 02:11 AM

can you use..

=AVERAGEIFS(K:K,M:M,"<="&C18,M:M,">="&F17)

k:k is the column to get the average
m:m is the dates column

"<="&C18 is the starting date
">="&F17 is the ending date

**dee101** · 05-07-2012, 02:37 AM

Hi vlady

Unfortunately that doesn't come out with quite the right answer. I tweaked it a bit to make the start time include the additional half hour (your formula started from 01/04/2007 0:00) but it only came out with the incorrect answer I was already coming up with :-(.

**vlady** · 05-07-2012, 09:08 PM

what is the your expected average for that?
95.03 ?

**dee101** · 05-07-2012, 09:28 PM

That's right, 95.03 is what I'm expecting, but I'm getting 95.02 using the formula.

**vlady** · 05-07-2012, 10:00 PM

it's including this

46.59 65.77 0 4/1/2007 0:00 @ row 13153 = 95.02

if you'll change 0:30 to 0:00 and use just
> without equal..

i can't deal with your workbook every time i open it the answer 95.02 turns to something like 67.66164322

if you could pull it like this
=AVERAGEIFS(K:K,M:M,">"&TEXT(F17+B12,"m/d/yyyy h:mm"),M:M,"<="&TEXT(C18+B13,"m/d/yyyy h:mm"))

note the > sign without =

make cell b12 starting time 0:00

**dee101** · 05-08-2012, 12:19 AM

Thanks so much. I've finally got this figured out. I didn't have immediate success with your formula (I was getting the 67.something figure too) but I had a realisation: the date figures, even though they look like "dd/mm/yyyy hh:mm", in the cell they're actually "dd/mm/yyyy hh:mm:ss". This was the tiny thing that was throwing out what would be included in the averages based on the dates.

The following formula works a treat:
=AVERAGEIFS(K2:K17521,M2:M17521,">"&(F17+B12),M2:M17521,"<"&(C18+B13))

I've attached the spreadsheet for interest.

2006-07 data - with working formula.xlsx

Thanks vlady!