# Averaging the last 5 smallest numbers

#### Trevor Norman

##### New Member
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

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### Eric W

##### MrExcel MVP
Welcome to MrExcel.

Try:

Book1
BCDEFGHIJKLMNOPQRSTUVWXYZ
5789478211563121134
6
73.4
Sheet3
Cell Formulas
RangeFormula
B7B7=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

##### New Member
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

##### MrExcel MVP
See if these work for you:

Book1
ABCDEFGHIJKLMNOPQRSTU
33-Aug2-Aug1-Aug31-Jul30-Jul29-Jul28-Jul27-Jul26-Jul25-Jul24-Jul23-Jul22-Jul21-Jul20-Jul19-Jul18-Jul17-Jul16-Jul15-Jul
4
5789478211563121134
6
7Average of most recent 5
87
9
10Average of 5 lowest of most recent 10
114.8
Sheet3
Cell Formulas
RangeFormula
B8B8=AVERAGE(B5:INDEX(5:5,AGGREGATE(15,6,COLUMN(\$B\$5:\$Z\$5)/(\$B\$5:\$Z\$5<>""),5)))
B11B11=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

##### New Member
Hi Eric
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

Replies
5
Views
152
Replies
9
Views
120
Replies
11
Views
184
Replies
12
Views
213
Replies
0
Views
97

### Forum statistics

1,127,589
Messages
5,625,671
Members
416,125
Latest member
NeedExcelHelp2021

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back