I have the following function, which calculates the average of the previous 7 rows of the column on the left.


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?

1 Answer 1


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.

  • 1
    I ended using if-isnumber instead of iferror: =map(B8:B; lambda(c; if(isnumber(c); average(offset(c;-6;0;7)); )))
    – ceving
    May 9, 2023 at 7:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.