Assistance with 28 day rolling average

jmomich

New Member
Joined
Sep 24, 2014
Messages
4
Hello - I wonder is somebody can help with a formula to calculate a 28 day rolling average? I have dates in rows across the top, about 90 days worth of data now. I also have a couple of weeks of empty days that I hide; this is so that I don't have to add a new column each day, I just unhide a single column the next morning when I need to add more data. The actual data starts in D5, and the columns with data go to BV5 at this point, however as mentioned I have empty columns hidden that go all the way to CM5. I want to be able to calculate the average of the last 28 days in the rows, in order to show a rolling average.

I've tried using =AVERAGE(OFFSET($D$5,COUNTA($D5:$CM35)-28,0,COUNTA($D5:$CM35)-27,1))

I've also tried using Index and Match in a couple of other attempts.

Any help would be appreciated.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Beyond_avarice

Board Regular
Joined
Nov 13, 2012
Messages
195
Office Version
  1. 2007
Platform
  1. Windows
Try this:

=AVERAGE(OFFSET(D5,0,COUNT(D5:CM5)-27,30,28))

This will work if you are adding data from left to right. After re-reading your question, I am suspecting that you are inserting columns and adding data to the left most side. If this is the case, the formula will have to be revised.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,708
Messages
5,524,431
Members
409,577
Latest member
Dwg

This Week's Hot Topics

Top