# How to Lock Cell References when Calculating an Average of a Percentage in Google Sheets

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:

=average(D15:I15)

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

I tried this:

=average(indirect("C12:I12"))

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

Any help would be appreciated :)

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

``````=average(D15:15)
``````

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:

``````    =average(query(sort(flatten(D15:15),flatten(column(D15:15)),0),
"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