Average a group of numbers within an date range.

  • Thread starter Thread starter sross
  • Start date Start date
S

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)))))
 
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
 
Other than some extra parentheses there's nothing wrong with your formula.

Did you enter it as an array?
 
IF? all one year, modify this to do it. Be SURE to array enter
=AVERAGE(IF(MONTH(A7:A1000)=2,(D7:D1000)))
 
Back
Top