| Home |
| Search |
| Today's Posts |
|
#1
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
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
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
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: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))))) |
|
#5
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))))) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| average a continuous group of numbers and negative numbers are 0 | Excel Worksheet Functions | |||
| Average of select range of numbers | Excel Worksheet Functions | |||
| Average the first nine numbers in a range. | Excel Discussion (Misc queries) | |||
| help how to crate group of date with Dynamic Range? | Excel Discussion (Misc queries) | |||
| Average first n numbers in a range (there may be less than n numbe | Excel Discussion (Misc queries) | |||
Linear Mode
