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! |
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. |
Function for generating monthly & weekly averages
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. |
Function for generating monthly & weekly averages
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. ;-) |
Function for generating monthly & weekly averages
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. ;-) |
All times are GMT +1. The time now is 02:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com