Function for generating monthly & weekly averages

[Guest]

I have a running list of dates in column A, but there are some missing dates.
There is associated data in column B. I'd like to put weekly averages
(Week is Sunday-Sat) in column C and monthly averages in column D.

I can't just fill down a pattern of every 7 or 30 days, because of the
missing days and the fact that not all months have 30 days.

Can you help me generate functions that will do this?

Thank you!

 

[Jay]

I have a running list of dates in column A, but there are some missing

dates.
There is associated data in column B. I'd like to put weekly
averages
(Week is Sunday-Sat) in column C and monthly averages in column D.

Try putting these in row 1 and copying down for as many rows as you need.

C1: =IF(E1=E2,"",SUMIF(E:E,E1,B:B)/COUNTIF(E:E,E1))

D1: =IF(F1=F2,"",SUMIF(F:F,F1,B:B)/COUNTIF(F:F,F1))

E1: =WEEKNUM(A1)

F1: =MONTH(A1)

The averages appear for the last day of each week or month.

 

[Guest]

Thank you Jay! That is exactly what I was looking for!

Cheers,
Heidi

 

[Jay]

Try putting these in row 1 and copying down for as many rows as you

Thank you Jay! That is exactly what I was looking for!

Two words of caution, though.

1. If your data spans more than 51 weeks, there'll be a problem because C1
goes by week of a year.

2. The calculation assumes that the week containing New Years Day is meant
to be split in two.

Adjustments for these potential pitfalls are straightforward. ;-)

 

[Guest]

Yup - I just ran into that when I tried it on my full data set! I had to
use =year(A1)&month(A1) and work with it that way to get unique months for
each year that could then use the sumif and countif functions.

Thanks for all your help Jay!

Heidi