11

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.

migrated from superuser.com Nov 20 '15 at 20:43

This question came from our site for computer enthusiasts and power users.

6

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 ! – Spongebob Squarepants 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. – Spongebob Squarepants Dec 1 '15 at 16:36
6

TL;DR

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

Example

enter image description here

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)

    enter image description here

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

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

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

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.