Need a formula to find bottom 5 values

[Hitesh_Banda]

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.)

 

[SteveO59L]

Have a look at small(), or you could use rank() and vlookup()

 

[Peter_SSs]

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]

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

 

[Peter_SSs]

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)

 

[Hitesh_Banda]

I didnt think about that...

My expected result would be

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

Any suggestions?

 

[Peter_SSs]

I didnt think about that...

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.