Macro to calculate and display the average

**suryab** · 02-20-2011, 11:36 PM

Hi all,

I have an excel sheet that has two columns (date and total records) and these records go back to 6 months. Each day there will be a new row with a date in first and total record in second column. I'm currently using below macro to compare today's record against the average of the previous 7 days. Since the data goes back to 6 months and it does not give me a clear vision of the changes, I wanted to change the macro to compare today's records against previous four weeks. If today is Monday, I wanted to take the average of past four Mondays (not everyday). I was wondering if someone can help me to modify this macro.

Please Login or Register  to view this content.

Thanks in advance.

**watersev** · 02-21-2011, 04:37 AM

hi, suryab, it would be nice to see sample workbook

**suryab** · 02-21-2011, 10:00 PM

Hi Watersev,

Thanks for your reply. The data is in this format and goes all the way back upto 6 months.

Date Records

14/02/2011 2629925
15/02/2011 2603934
16/02/2011 2548989
17/02/2011 2597713
18/02/2011 2566184
19/02/2011 1154049

**watersev** · 02-22-2011, 04:39 AM

hi, Suryab, I would suggest to use formula that will give you averages:
1. 1 week to date
2. 2 weeks to date
3. 3 weeks to date
4. 4 weeks to date

**suryab** · 02-22-2011, 06:14 PM

Hi Watersev,

Thanks for your time and looking into this. The macro code I've quoted above is a part of a larger macro that does many other things so using the formula may be little harder to accommodate. I wanted to modify the macro instead if that is possible.

Thank you.