Averaging the last 5 smallest numbers

Trevor Norman

New Member
Joined
Aug 6, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

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

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,753
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
Joined
Aug 6, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Aug 18, 2015
Messages
10,753
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
Joined
Aug 6, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top