|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 22nd, 2009, 10:05 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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))))) 
|
|
#2
April 22nd, 2009, 10:18 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)))))
|
|
#3
April 22nd, 2009, 10:23 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)))))
|
|
#4
April 22nd, 2009, 10:26 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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  1000)))-- 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)))))
|
|
#5
April 22nd, 2009, 11:16 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)))))
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
