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, 2015 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, 2015 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, 2015 at 16:36

TL;DR

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

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, 2015 at 23:15
• it worked fine, but once I added another cell, it did not update properly? Nov 24, 2015 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)))