Averaging the last 5 smallest numbers

[Trevor Norman]

I refer to the post Averaging lowest values which I an trying to replicate ie I wish to average the smallest 5 numbers out of the last 10, with the possibility that there may be blanks in between, in which case I need the last 10 numbers.
I have copied the formulea however I get a message that excel is out of resource.
I have also tried to limit the number of cells to check on, say 50, as opposed to the complete column but the I have an #REF error.
I would prefer if the range of ceels to check was a row, as opposed to a column, say B5 to B55
Any help appreciated
Thank you

 

[Eric W]

Welcome to MrExcel.

Try:

Book1
	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
5	7	8		9	4		7	8	2	11		5	6	3		12	11	3	4
6
7	3.4
Sheet3

Cell Formulas
Range	Formula
B7	B7	=AVERAGE(SMALL(INDEX(5:5,AGGREGATE(14,6,COLUMN(B5:Z5)/(B5:Z5<>""),10)):Z5,{1,2,3,4,5}))

This is set up for a row. If you want it for a column (B5:B55), let me know.

 

[Trevor Norman]

Hi Eric
Many thanks for you quick reply
I see that the formula takes the last 10 numbers from the last cell with a value in this case T5 and works back towards B5,
In my spreadsheet the date is arranged in date order with the most recent in B5 and the oldest in Z5, and what I should have said is I require the 5 most recent, and not the last 5, in other words it should work the reverse to current and start the search for numbers in B5.

Also grateful if you could advise the change to the formula if I required more smallest numbers from a larger range, say the 10 smallest from the recent 15
Many thanks
Trevor Norman

 

[Eric W]

See if these work for you:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
3		3-Aug	2-Aug	1-Aug	31-Jul	30-Jul	29-Jul	28-Jul	27-Jul	26-Jul	25-Jul	24-Jul	23-Jul	22-Jul	21-Jul	20-Jul	19-Jul	18-Jul	17-Jul	16-Jul	15-Jul
4
5		7	8		9	4		7	8	2	11		5	6	3		12	11	3	4
6
7		Average of most recent 5
8		7
9
10		Average of 5 lowest of most recent 10
11		4.8
Sheet3

Cell Formulas
Range	Formula
B8	B8	=AVERAGE(B5:INDEX(5:5,AGGREGATE(15,6,COLUMN($B$5:$Z$5)/($B$5:$Z$5<>""),5)))
B11	B11	=AVERAGE(SMALL(B5:INDEX(5:5,AGGREGATE(15,6,COLUMN($B$5:$Z$5)/($B$5:$Z$5<>""),10)),{1,2,3,4,5}))

If you have trouble adapting them, let me know.

 

[Trevor Norman]

Hi Eric
Many thanks for your help
Have tested a number of scenarios and all appears exactly as I wanted.

Would have taken me a very long time to have sorted out

Once again many thanks

Trevor Norman