Need a formula to find bottom 5 values

Hitesh_Banda

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

SteveO59L

Well-known Member
Have a look at small(), or you could use rank() and vlookup()

Peter_SSs

MrExcel MVP, Moderator
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?

Hitesh_Banda

New Member
Yeah.. if there are ties, I have a planned result, with same formula.

Peter_SSs

MrExcel MVP, Moderator
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,....
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)

Hitesh_Banda

New Member

My expected result would be

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

Any suggestions?

Peter_SSs

MrExcel MVP, Moderator

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.

Replies
1
Views
151
Replies
5
Views
651
Replies
1
Views
241
Replies
4
Views
256
Replies
1
Views
254

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.

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