Averaging List with Blank Cells

[Mr. X]

My apologies if this is a repeat post. I'm having news reader
problems.

I was given this formula for averaging the last five numbers in a
column of numbers:

=AVERAGE(OFFSET(A2,COUNTA(A:A)-5,0,5,1))

It works fine unless there are some blank cells like below:

1. 3
2.
3.
4. 6
5. 7
6. 8
7.
8. 35
9.
10. 11

Is there a way to change it so that it will average the last five
numbers in this column and ignore the blank cells?

Thanks for your help.

Frank

 

[Harlan Grove]

I was given this formula for averaging the last five numbers in a
column of numbers:

=AVERAGE(OFFSET(A2,COUNTA(A:A)-5,0,5,1))

It works fine unless there are some blank cells like below:

1. 3
2.
3.
4. 6
5. 7
6. 8
7.
8. 35
9.
10. 11

Is there a way to change it so that it will average the last five
numbers in this column and ignore the blank cells?

The formula you were given only works when there are no blank cells between
the numbers. If you need to find the last 5 numbers net of intervening blank
cells, you'll need something like the following array formula.

=AVERAGE(OFFSET(A2,LARGE(ISNUMBER(A2:A65536)*ROW(A2:A65536),5)-2,0,
MATCH(1E+300,A2:A65536)-LARGE(ISNUMBER(A2:A65536)*ROW(A2:A65536),5)+2,1))

 

[Frank West]

The formula you were given only works when there are no blank cells between
the numbers. If you need to find the last 5 numbers net of intervening blank
cells, you'll need something like the following array formula.

=AVERAGE(OFFSET(A2,LARGE(ISNUMBER(A2:A65536)*ROW(A2:A65536),5)-2,0,
MATCH(1E+300,A2:A65536)-LARGE(ISNUMBER(A2:A65536)*ROW(A2:A65536),5)+2,1))

Well, this works, thank you very much.

I'm not saying I understand it, but it works.

Interesting.

Frank