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!

=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

<(E-Mail Removed)> wrote in message
news:90da568f-0600-4cba-b724-(E-Mail Removed)...
>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!

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.

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

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



"Infinitogool" <(E-Mail Removed)> wrote in message
news:uHMKno8%(E-Mail Removed)...
> 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.
>
>> 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!

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
--
Regards
Roger Govier

<(E-Mail Removed)> wrote in message
news:90da568f-0600-4cba-b724-(E-Mail Removed)...
> 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!

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


"(E-Mail Removed)" wrote:

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

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

"Billy Liddel" wrote:

> 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
>
>
> "(E-Mail Removed)" wrote:
>
> > 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!
> >

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

--
Regards
Roger Govier

"Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message
news4C8361C-27F8-4458-BFC5-(E-Mail Removed)...
> 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
> --
> Regards
> Roger Govier
>
> <(E-Mail Removed)> wrote in message
> news:90da568f-0600-4cba-b724-(E-Mail Removed)...
>> 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!
>