Easiest way to get 3rd col = monthly averages, from cols 1, 2 = weekly date, data?

[sherifffruitfly]

Hi all,

I've got years of data of the following sort:

1/2/98, 55.0
1/9/98, 53.2
1/16/98, 43.5
1/23/98, 45.2
1/30/98, 64.3
..
..
..
(and so on, up to the present)

I would like a 3rd column, with an entry on each row representing the
last week of a month, whose value is the average of the data points
for that month. Note that the number of weeks in a month varies.


Greatly appreciating any quick & easy approaches,

cdj

 

[Roger Govier]

Hi

Rather than trying to do it on all the rows that are the last week of a
given month, in column D starting at D2 enter
01/01/98
Format>Cells>Number>Custom> yyyy-mmm
Right click on the fill handle (small black cross when you hover over
bottom right of cell) and fill downward.
When you release the mouse button, choose the Option Fill Months

In cell E2 enter
=SUMPRODUCT(--(TEXT($A$1:$A$1000,"yyyy-mmm")=TEXT(D2,"yyyy-mmm"),$B$1:$B$1000)/
SUMPRODUCT(--(TEXT($A$1:$A$1000,"yyyy-mmm")=TEXT(D2,"yyyy-mmm")
Fill down column E for the extent that you have set up months in column
D

Change ranges of A1:A100 and B1:B1000 to match the range of your data.

 

[sherifffruitfly]

In cell E2 enter
=SUMPRODUCT(--(TEXT($A$1:$A$1000,"yyyy-mmm")=TEXT(D2,"yyyy-mmm"),$B$1:$B$1000)/
SUMPRODUCT(--(TEXT($A$1:$A$1000,"yyyy-mmm")=TEXT(D2,"yyyy-mmm")
Fill down column E for the extent that you have set up months in column
D

Change ranges of A1:A100 and B1:B1000 to match the range of your data.

Thanks, Roger. I'm trying to work out some sort of error I'm getting
from your function. Here's my adaptation of your function, reflecting
the cells I'm actually using:

=SUMPRODUCT(--(TEXT($A$15:$A$479,"yyyy-mmm")=TEXT(N15,"yyyy-mmm"),$B
$15:$B$479)/SUMPRODUCT(--(TEXT($A$15:$A$479,"yyyy-mmm")=TEXT(N15,"yyyy-
mmm")

Legend:

a15-a479 - the weekly dates
b15-b479 - the weekly data
n15 - the cell with 1998-Jan in it (runs thru n121, 2006-Nov)
o15 - the cell I'm trying the above formula in.

 

[sherifffruitfly]

Thanks, Roger. I'm trying to work out some sort of error I'm getting
from your function. Here's my adaptation of your function, reflecting
the cells I'm actually using:

=SUMPRODUCT(--(TEXT($A$15:$A$479,"yyyy-mmm")=TEXT(N15,"yyyy-mmm"),$B
$15:$B$479)/SUMPRODUCT(--(TEXT($A$15:$A$479,"yyyy-mmm")=TEXT(N15,"yyyy-
mmm")

Nevermind - either you or I dropped a parenthesis at some point.
Properly parenthesized, it works fine.

Thanks a jillion!