Excel Function to Average 6 Cells to the left of target cell-CodePudding

I have a spreadsheet of data which includes information from the last few years. Each month, a new column is added, and a lot of the AVERAGE formulas break.

Essentially, what I need is a function that will select 6 cells based on their proximity to the function cell.

Ex: The function in Cell J should select the 6 cells to its left (same row), and if a new column is added, moving this function to K, it should continue to only draw from the 6 cells to its left, potentially including the new one.

CodePudding user response：

Assuming at least 6 columns, try:

=AVERAGE(INDEX(1:1,SEQUENCE(6,,COLUMN()-6)))

Or, 1 upto 5 columns on the left only:

=AVERAGE(INDEX(1:1,LET(X,SEQUENCE(6,,COLUMN()-6),FILTER(X,X>0))))

Ignore potential empty cells between formula and last 6 values; empty cells in between values themselves are also ignored:

=AVERAGE(LET(X,INDEX(1:1&"",SEQUENCE(COLUMN()-1)),Y,--FILTER(X,X<>""),INDEX(Y,SEQUENCE(MIN(COUNT(Y),6),,COUNT(Y),-1))))

CodePudding user response：

So, heavily based on the answer by @JvdV, but wanted to make it a bit more independent so it now works in other columns.

Needed to label the columns and then use month() and now() with vlookup. Also works when on a different sheet - but have to add the sheet names to the ranges.