Need a formula to find bottom 5 values

Hitesh_Banda

New Member
Joined
Jun 8, 2010
Messages
25
Hi,

Is there a formula(s) which can help me find bottom 5 values from a set of values?

Lets say I have a set of 20 values in % format. And there are 20 such rows. Now, I need to find the bottom 5 of each 20 values of each row. (Hope this is not confusing. If any problem, please let me know.)
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
59,294
Office Version
  1. 365
Platform
  1. Windows
Hi,

Is there a formula(s) which can help me find bottom 5 values from a set of values?

Lets say I have a set of 20 values in % format. And there are 20 such rows. Now, I need to find the bottom 5 of each 20 values of each row. (Hope this is not confusing. If any problem, please let me know.)
Have you thought about waht you want to happen if there are ties?
 
Upvote 0

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
59,294
Office Version
  1. 365
Platform
  1. Windows
Yeah.. if there are ties, I have a planned result, with same formula. :)
That didn't really clarify for me. If the data was
1,1,1,2,2,2,3,4,5,6,7,....
Would your expected result be
a) 1,1,1,2,2 (first 5 when ordered)
b) 1,1,1,2,2,2 (first 5 plust ties for 5th when ordered)
c) 1,2,3,4,5 (5 lowest actual values)
 
Upvote 0

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
59,294
Office Version
  1. 365
Platform
  1. Windows
:eek: I didnt think about that... :confused:

My expected result would be

a) 1,1,1,2,2 (first 5 when ordered)

Any suggestions?
Yes, focus on the SMALL() function as mentioned by SteveO59L. Look in the built-in Help and you will probably be able to figure it out for yourself but if you cannot, post back and tell us what you have tried.
 
Upvote 0

Forum statistics

Threads
1,190,783
Messages
5,982,894
Members
439,804
Latest member
melD94

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