Function for generating monthly & weekly averages

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!

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

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

Cheers,
Heidi

"Jay" wrote:

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

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

> 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. ;-)

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

"Jay" wrote:

> >> 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.
>
> > 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. ;-)