an average with a dynamic range (which excludes blank)

[akardar1]

Hi Wizards,

I have a column T with values like:

5
6
7
blank
9
7
blank
12
15

I would like an average of the last 5 values... but excluding blanks!
So... if I am at the bottom of the the list.... I want an average of 15,12,7,9 and 7.

If I use a simple =average of 5 value, it would be 15,12, blank, 7, 9
... which is not good because i want 5 numeric values for my average, i don't want the blank

 

[maabadi]

Use this and Press CTRL+SHIFT+ENTER

=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(A4:A16),ROW(A4:A16)),{1,2,3,4,5}),ROW(A4:A16), A4:A16))

 

[akardar1]

Wow - that's a beast and works a treat - thank you

 

[akardar1]

If i wanted to look a at the last "30 non-blank values", instead of 5, do I need to
change {1,2,3,4,5}
to {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,19,30}
?

 

[maabadi]

Yes. It is.

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Another alternative that does not require the Ctrl+Shift+Enter confirmation - & just change W1 to get 30 if that is what you want (provided there are at least 30 numbers in the range. )

20 12 20.xlsm
	T	U	V	W
1	5		No. to average	5
2	6		Average	10
3	7
4
5	9
6	7
7
8	12
9	15
10
Average Last N

Cell Formulas
Range	Formula
W2	W2	=AVERAGE(INDEX(T:T,AGGREGATE(14,6,ROW(T1:T1000)/(T1:T1000<>""),W1)):T1000)