I am tracking a percentage over time, adding a new column every week. I'd like to track the most recent six percentages as an average.

When I add a new column, even a cell reference locked with a $ changes.

For other numbers, ones that are not percentages, I'm successfully using INDIRECT. But for percentages, I'm getting an error message saying it's trying to divide by zero.

I've played around with OFFSET but haven't had luck there either.

I want this:


...but where the I doesn't become a J when I add a new column into D.

I tried this:


...which works for other kinds of number formats, but not percentages.

Any help would be appreciated :)


1 Answer 1



Average() takes into consideration only the filled cells, so you can also directly use


and get the average of all ( and only ) the filled cells of the row.

To find the average of only the last 6 values ​​of that row, you could use this formula:

 "limit 6"))

If you're adding the newer column in the first place, the formula is:

=average(query(transpose(indirect("D15:15")),"limit 6"))

test file


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.