# Thread: Average last 5 non-blank cells Thanks: 0 Likes: 0

1. ## Average last 5 non-blank cells

 1 1 0 1 1 0

Hello there,

My goal is to get the 60.00% but what formula would I use to exclude blanks and only consider the bottom 5 values?

Thanks!

2. ## Re: Average last 5 non-blank cells

See post # 5 in attached link.
https://www.excelforum.com/excel-for...ank-cells.html

 A B C 1 1 2 1 0.6 3 4 0 5 1 6 7 1 8 0 9 10

 Cell Formula C2 {=AVERAGE(IF(ROW(A1:A12)>=LARGE(IF(A1:A12<>"",ROW(A1:A12)),MIN(COUNT(A1:A12),5)),IF(A1:A12<>"",A1:A12)))}
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

Excel tables to the web >> Excel Jeanie HTML 4

3. ## Re: Average last 5 non-blank cells

Another way:

 A B C 1 1 2 1 0.6 3 4 0 5 1 6 7 1 8 0

 Cell Array Formula C2 {=AVERAGE(INDEX(A1:A12,LARGE(IF(A1:A12<>"",ROW(A1:A12)),5)):A12)}

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself