View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jay
 
Posts: n/a
Default 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.


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.