Assistance with 28 day rolling average

jmomich

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

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

Replies
7
Views
39
Replies
10
Views
175
Replies
3
Views
86
Replies
1
Views
40
Replies
2
Views
155