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(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

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

"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

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

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