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