Calculating Monthly Average from Daily Values

[77m.grub]

I have a worksheet with dates in column A, values in column B. The
dates correspond to the same day of the week (Tuesday) for the entire
calendar year. Like this --

1/1/2008 54
1/8/2008 65
1/15/2008 55
1/22/1008 56
1/29/2008 59
2/5/2008 61
2/12/2008 72
2/19/2008 77
2/26/2008 76
etc.

I want to add a Column C which calculates the monthly average for
January, then the monthly average for February, etc. Not a rolling
average, but simply an average for the month. So January's average
would be 57.8, February's would be 71.5.

Problem is that I have over 100 years (!) of data and obviously each
month contains different number of weeks. Is there a way to do this
without averaging each month individually?

Thanks for any guidance you can provide!

 

[Peo Sjoblom]

=AVERAGE(IF(ISNUMBER(A2:A10)*(MONTH(A2:A10)=2),B2:B10))


entered with ctrl + shift & enter will give you the average for February any
year, if you want
to specify a certain year you can use


=AVERAGE(IF((YEAR(A2:A10)=2008)*(MONTH(A2:A10)=2),B2:B10))



--


Regards,


Peo Sjoblom

 

[Infinitogool]

Hi
Try
data A1:B100

January
=SUMPRODUCT(--(MONTH(A1:A100)=1),B1:B100)/SUMPRODUCT(--(MONTH(A1:A100)=1))
February
=SUMPRODUCT(--(MONTH(A1:A100)=2),B1:B100)/SUMPRODUCT(--(MONTH(A1:A100)=2))
.........

Or
C1:
=SUMPRODUCT(--(MONTH($A$1:$A$100)=ROW()),$B$1:$B$100)/SUMPRODUCT(--(MONTH($A$1:$A$100)=ROW()))
and copy down as needed (C12)

Regards,
Pedro J.

 

[Peo Sjoblom]

Just a heads up, if there by any chance are any blank cells in the date
column your first
formula will include those as January

--


Regards,


Peo Sjoblom

 

[Roger Govier]

Hi

I think I would use a helper column in C with the formula
=MONTH(A1)
copied down
Then in D1
=SUMIF(C:C,ROW(D1),B:B)/COUNTIF(C:C,ROW(D1))
This will give the Average for Month 1 - January
Copy down through D212 to get the Averages for months 2 to 12

 

[Billy Liddel]

Hi

I used the table set as:

Date Cost Months 2007 2008
01/12/2007 45 Jan 57.8
05/12/2007 55 Feb 71.5
01/01/2008 54 Mar
08/01/2008 65 Apr

Headings in row 3; formula in D4:
=IF(SUMPRODUCT(--(YEAR($A$4:$A$14)=D$3),--(MONTH($A$4:$A$14)=ROWS($1:1)))=0,"",SUMPRODUCT(--(YEAR($A$4:$A$14)=D$3),--(MONTH($A$4:$A$14)=ROWS($1:1))*($B$4:$B$14)/SUMPRODUCT(--(YEAR($A$4:$A$14)=2007),--(MONTH($A$4:$A$14)=ROWS($1:1)))))

Copy down and across.

regards
Peter Atherton

 

[Billy Liddel]

Woops I forgot to change the 2007 to a cell reference in the divisor. Should
be:

=IF(SUMPRODUCT(--(YEAR($A$4:$A$14)=D$3),--(MONTH($A$4:$A$14)=ROWS($1:1)))=0,"",SUMPRODUCT(--(YEAR($A$4:$A$14)=D$3),--(MONTH($A$4:$A$14)=ROWS($1:1))*($B$4:$B$14)/SUMPRODUCT(--(YEAR($A$4:$A$14)=D$3),--(MONTH($A$4:$A$14)=ROWS($1:1)))))

Peter Atherton

 

[Roger Govier]

Very good point, Peo about the blank cells.
My first formula in column C should be
=IF(A1="","",MONTH(A1))