Last nth number avg

[ParkourAngel]

I have a large set of values (productivity % of each person) and I want to be able to pull the average of the last 13 values. How do I ignore the blanks?

 

[Phuoc]

Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1
2	8%	1%	2%	3%			4%	5%	6%		7%	8%	9%	10%		11%		12%	13%		14%
Sheet1

Cell Formulas
Range	Formula
A2	A2	=AVERAGE(IF((B2:Z2<>"")*COLUMN(B2:Z2)>= AGGREGATE(14,6,COLUMN(B2:Z2)/(B2:Z2<>""),13),B2:Z2,""))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[plettieri]

test

 

[ParkourAngel]

Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1
2	8%	1%	2%	3%			4%	5%	6%		7%	8%	9%	10%		11%		12%	13%		14%
Sheet1

Cell Formulas
Range	Formula
A2	A2	=AVERAGE(IF((B2:Z2<>"")*COLUMN(B2:Z2)>= AGGREGATE(14,6,COLUMN(B2:Z2)/(B2:Z2<>""),13),B2:Z2,""))
Press CTRL+SHIFT+ENTER to enter array formulas.

This worked out great thanks!!!

 

[Peter_SSs]

If you didn't want the Ctrl+Shift+Enter array entry then I think this standard-entry modification should also work for you

24 07 23.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1
2	8%	1%	2%	3%			4%	5%	6%		7%	8%	9%	10%		11%		12%	13%		14%
Average

Cell Formulas
Range	Formula
A2	A2	=AVERAGE(INDEX(2:2,AGGREGATE(14,6,COLUMN(B2:Z2)/(B2:Z2<>""),13)):Z2)