Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average values in date range
Hi
I have a list of numerical values (P5:P200) in O5:O200 is a corresponding date as to when the values were recorded. How can I calculate an average of values collected within a specific month. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average values in date range
=AVERAGE(IF(MONTH($O$5:$O$200)=1,$P$5:$P$200))
This is for January. Change 1 to 2 for Feb, 3 for March etc. It is an array formula and needs CTRL+SHIFT+ENTER not just ENTER. "sarahg" wrote in message ... Hi I have a list of numerical values (P5:P200) in O5:O200 is a corresponding date as to when the values were recorded. How can I calculate an average of values collected within a specific month. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average values in date range
Try,
=SUMPRODUCT(--(ISNUMBER(P5:P200)),--(MONTH(P5:P200)=1),O5:O200) Mike "sarahg" wrote: Hi I have a list of numerical values (P5:P200) in O5:O200 is a corresponding date as to when the values were recorded. How can I calculate an average of values collected within a specific month. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average values in date range
Ignore the last answer which wasn't the question you asked:)
This hopefilly is =AVERAGE(IF((MONTH(P5:P200)=1)*ISNUMBER(P5:P200),( O5:O200),"")) This is an array so commit with CTRL+Shift+Enter Not by simply pressing enter. Mike "Mike H" wrote: Try, =SUMPRODUCT(--(ISNUMBER(P5:P200)),--(MONTH(P5:P200)=1),O5:O200) Mike "sarahg" wrote: Hi I have a list of numerical values (P5:P200) in O5:O200 is a corresponding date as to when the values were recorded. How can I calculate an average of values collected within a specific month. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average values in date range
Thanks Guarav,
Works for all months except January - not all of the cells contain data yet. I have a sumproduct formula calculating something else and know that you add --(YEAR(RP!$E$5:$E$200)=2009) into the formula. is something similar required in this, and if so what/where. "Gaurav" wrote: =AVERAGE(IF(MONTH($O$5:$O$200)=1,$P$5:$P$200)) This is for January. Change 1 to 2 for Feb, 3 for March etc. It is an array formula and needs CTRL+SHIFT+ENTER not just ENTER. "sarahg" wrote in message ... Hi I have a list of numerical values (P5:P200) in O5:O200 is a corresponding date as to when the values were recorded. How can I calculate an average of values collected within a specific month. Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average values in date range
"Works for all months except January"
what do you mean? what does it return? "sarahg" wrote in message ... Thanks Guarav, Works for all months except January - not all of the cells contain data yet. I have a sumproduct formula calculating something else and know that you add --(YEAR(RP!$E$5:$E$200)=2009) into the formula. is something similar required in this, and if so what/where. "Gaurav" wrote: =AVERAGE(IF(MONTH($O$5:$O$200)=1,$P$5:$P$200)) This is for January. Change 1 to 2 for Feb, 3 for March etc. It is an array formula and needs CTRL+SHIFT+ENTER not just ENTER. "sarahg" wrote in message ... Hi I have a list of numerical values (P5:P200) in O5:O200 is a corresponding date as to when the values were recorded. How can I calculate an average of values collected within a specific month. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average of multiple range with error values | Excel Worksheet Functions | |||
select date range then find average of values in another cell | Excel Worksheet Functions | |||
Sum values within a specified date range | Excel Worksheet Functions | |||
Basing Average function range on Date? | Excel Discussion (Misc queries) | |||
Can you average data in 1 column based on a range of values in another? | Excel Worksheet Functions |