Average a group of numbers within an date range.

[sross]

I have a spreadsheet that has tickets for an entire year. I have calculated
a field that has the total number of days a ticket was open for each ticket.
I am trying to average the total number of days a ticket is open by month,
to have an average number of days a ticket is open per month. I have tried
this calculation but am not getting a valid number. My dates are in
month/day/year format. 01/01/09. Thanks for your help

=IF(B2=0,0,(AVERAGE(IF((MONTH(Data!$O$3:$O$4834)=1)*(YEAR(Data!$O$3:$O$4834)=2009),(Data!$AT$3:$AT$4834)))))

 

[Mike H]

Hi,

Your formula is fine, it's the way you are entering it, it's an array

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

 

[T. Valko]

Other than some extra parentheses there's nothing wrong with your formula.

Did you enter it as an array?

 

[Don Guillett]

IF? all one year, modify this to do it. Be SURE to array enter
=AVERAGE(IF(MONTH(A7:A1000)=2,(D71000)))

 

[sross]

Yes, this is what I needed to do, everything is working correctly now.
THANK YOU!!!