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

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

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

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

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

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