0

I have Google Sheet connected via Zapier to automatically add a new row to my spreadsheet everyone time someone fills out a satisfaction survey on my website. I have =AVERAGE(F3:F) as the top row to show running averages of numerical values.

Problem is, when Zapier adds a new row, it seems to use the "Insert 1 Above" on Row 3, thus the formula changes automatically to =AVERAGE(F4:F) and the cell outputs #DIV/0! divide by zero error. How to avoid this?

Improve this question
0

1 Answer 1

Reset to default
0

Indeed, the range F3:F will become F4:F if a new row is inserted above the 3rd. You should change either the insertion rule or the range, so that insertion happens inside the range.

If your cell F2 does not have numeric data (chances are it's either the name of the column or is blank), then you can use average(F2:F) which give the same result as average(F3:F) but will auto-expand at insertion.

A solution not relying on the content of F2 is 

=(sum(F2:F)-F2)/(count(F2:F)-count(F2))

This formula always agrees with average(F3:F), and is preserved by insertion above row 3.

Improve this answer

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