Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
I have the following function, which calculates the average of the previous 7 rows of the column on the left.
=AVERAGE(OFFSET(C8;-6;-1;7))
The problem: whenever I update the data column I have to update the average column in order to copy the formula.
I would like to fix this by using ARRAYFORMULA, but I do not know how to specify the current cell for OFFSET?
Use map() instead of an array formula. To calculate a rolling 7-day average of the values in column B2:B, put this formula in row 8 of a free column:
=map(B8:B, lambda(c, iferror(average(offset(c, -6, 0, 7)))))
The map() function gets references instead of values which lets offset() work properly in the enclosed lambda function.
=map(B8:B; lambda(c; if(isnumber(c); average(offset(c;-6;0;7)); )))