averageif for date range

[tbaron]

Hi all

I'm having difficulties in making a formula that will calculate the average values based on a date range query. Attached is an example of the sheet, what I need is the average values from column "E" if the dates in column "A" are within that fiscal year. What I have so far is as follows:

=averageif(($A$7:$A$11>=DATEVALUE("1/07/2010"))*($A$7:$A$11<=DATEVALUE("30/6/2011")),$E$7:$E$11)

but is obviously wrong! I’m using Excel 03 and not sure if the averageif function will work on this version?!

avgif date range example.xls

With thanks in advance

Tony

[daddylonglegs]

You can't use AVERAGEIF in Excel 2003 but you can do it almost the same way with an "array formula"

=AVERAGE(IF(($A$7:$A$11>=DATE(2010,7,1))*($A$7:$A$11<DATE(2011,7,1)),$E$7:$E$11))

confirm that with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar

[tbaron]

Thanks for the quick response, and I've tested with an array however get a "value not available" error. Do I need to amend my date to the US format (yyyy,mm,dd) or visa versa?

[ConneXionLost]

The data format should not change since the DATE function only uses year,month,day for the function arguments in that arrangement.

Please ensure you have entered the formula as an array formula by using Ctrl-Shift-Enter.

[tbaron]

... Thanks again, not sure what I did with the copy and past but its working now.


Cheers DLL.

Tony