Average a group of numbers within an date range.
 

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$4 834)))))

Average a group of numbers within an date range.
 

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

"sross" wrote:

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$4 834)))))

Average a group of numbers within an date range.
 

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

Did you enter it as an array?

--
Biff
Microsoft Excel MVP


"sross" wrote in message
...
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$4 834)))))

Average a group of numbers within an date range.
 

IF? all one year, modify this to do it. Be SURE to array enter
=AVERAGE(IF(MONTH(A7:A1000)=2,(D7:D1000)))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"sross" wrote in message
...
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$4 834)))))

Average a group of numbers within an date range.
 

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

"Mike H" wrote:

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

"sross" wrote:

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$4 834)))))