Shifting Average Function when adding a new column

[tkoral]

Hello,

First off, thanks for taking the time to check this out.

What I'm trying to do is create an average formula that shifts as I add columns. I have a "10 Week Average" that I continue to add the latest week to, but I always need to manually shift my formula to the latest 10 columns. I've attached a sample of the weeks and how a column is added.

Any help would be super appreciated!

Thanks!

[CK76]

You can use OFFSET() function and INDEX() function to make range dynamic.

In M6:
=IFERROR(ROUND(AVERAGE(OFFSET(M6,0,-10,1,10)),0),0)

Copy Down.

In N6:
=ROUND(AVERAGE($G6:INDEX($C6:OFFSET(N6,0,-1),MATCH(DATE(2020,10,1),$C$5:OFFSET(N6,5-ROW(),-2),1))),0)

Copy down.

NOTE: You will need to adjust Date() for different Quarter.

Personally, I'd recommend that you restructure data to flat table (i.e. Single Date column, rather than growing data across).

[tkoral]

Hi CK76,

This worked like a charm. I super super appreciate the help and input!

Thank you!