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

Question

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.

Answer 1

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.

Answer 2

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))

Answer 3

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))

Answer 4

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