0

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?

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
1
  • 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 at 7:33

Your Answer

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

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