# Average of last 7 non-empty non-blank cells in Google Sheets

I have numbers in range D7:D and there are times when some cells are empty. I want the average of the last 7 numbers but to skip blanks. So if in the last 7 there's only 3 that are full, I want it to go back further and find 7 total and average them out.

Here is one approach:

``````=average(indirect("D" & iferror(large(filter(row(D7:D), len(D7:D)), 7), row(D7)) & ":D"))
``````

Explanation:

• `filter(row(D7:D), len(D7:D))` returns an array that consists of the row numbers of the nonempty entries in the given range.
• `large(..., 7)` picks the 7th largest number from this array: this is the row number where you want to start averaging.
• `iferror(..., row(D7))` is a safeguard in case your range has fewer than 7 non-blank entries: in this case, the averaging will begin with D7. I could have just put "7" instead of `row(D7)`, but `row(D7)` makes the formula more portable in case you decide to copy it elsewhere.
• `indirect("D" & ... & ":D")` forms the range for averaging, e.g., `D9:D` if the output of preceding computation was 9.
• Finally, `average` does the average. You could put other aggregate functions here, too.
• works & updates to new changes ! Nov 24 '15 at 15:30
• Thanks, but now I think JPV's answer should be the accepted one: it's simpler and more semantically natural.
– user79865
Nov 25 '15 at 1:20
• it did not update properly for me, I thought it was easier too but once I added more numbers it did not work. Dec 1 '15 at 16:36

### TL;DR

``````=AVERAGE(OFFSET(A:A;COUNT(A:A)-7 + MATCH(TRUE;INDEX(A:A<>0);0)-1;0))
``````

### Example ### Explanation

• First we count how many numbers are in row A with `=COUNT(A:A)`. Cells containing text won't be counted. Our example would give us `9`
• From this number we subtract 7 since you want the average of the last 7 values: `=COUNT(A:A)-7`. This will give us a `2`.
• Now we use the offset function to get a range of a complete column but with an offset of 2 cells which effectively means we have only a part of a column left with exactly 7 values remaining

Imagine a column without blank cells before the first cell with values. Then a simplified example could look like `=OFFSET(A:A; COUNT(A:A)-7 ;0)`. But this won't work as soon as the column starts with blank cells like shown in the picture above

• We now need to extend our offset by the number of blank cells. That doesn't mean we count all blank values in a row. Just the ones before the first non-empty cell.

You can count them with `=MATCH(TRUE;INDEX(A:A<>0);0)-1` and get `6` as result or our example

Now we modify our offset function and add this count to get the correct range containing only 7 values
`OFFSET(A:A; COUNT(A:A)-7 + MATCH(TRUE;INDEX(A:A<>0);0)-1 ;0)` • As last step we calculate the average of this range. Our example would give us 4 as result since the average of 1,2,3,4,5,6,7 is 4.
`=AVERAGE(OFFSET(A:A;COUNT(A:A)-7 + MATCH(TRUE;INDEX(A:A<>0);0)-1;0))`

– ale
Nov 20 '15 at 23:15
• it worked fine, but once I added another cell, it did not update properly? Nov 24 '15 at 15:29

Yet another way:

``````=AVERAGE(ARRAY_CONSTRAIN(SORT(FILTER(A:A, ISNUMBER(A:A)), 1, 1), 7, 1))
``````

Explantion:

1. filter out all numeric values
2. turn the column upside down (so that last values are above) by using sort()
3. limit the output of that column to 7 rows (using ARRAY_CONSTRAIN)
4. Take the average ...

EDIT: Sorry for misunderstanding. The above averages the LARGEST 7 values, not the last 7 values in a column. To average the last 7 values Normal Human's solution is great. An alternative way with less functions would be to use QUERY()

Assuming the numeric data in column A, try:

``````=AVERAGE(QUERY(ARRAYFORMULA({ROW(A:A),A:A}),"select Col2 where Col2 is not null order by Col1 desc limit 7", 0))
``````

``````=AVERAGE(IFERROR(QUERY(D7:D,
"where D is not null
offset "&COUNTA(QUERY(D7:D, "where D is not null", 0)) - 7, 0)))`````` 