Find real n-th smallest/largest number

[Rudi]

Hi,

How could I find (for example) the third smallest value in a list of numbers even if there is duplicates. For example, LARGE and SMALL find the number but if I had numbers 1, 2, 2, 3, 4 and I use SMALL(Range, 3), then it gives 2. I want 3? RANK does not help either!

Any ways to do this?

TX

[sdckapr]

Make a new range that just lists teh unique values and then get the small/large of this:

If your cells are in A1:A5, In B1 enter:
=IF(COUNTIF($A$1:A1,A1)=1,A1,"")

Then copy to B2:B5

Then use:
=SMALL(B1:B5,3)

Steve

[HansV]

Or without an auxiliary column, you could use this array formula (confirm with Ctrl+Shift+Enter):

=SMALL(IF(MATCH(A1:A5,A1:A5,0)=ROW(A1:A5),A1:A5),3)

[Rudi]

Awesome.

Thanks guys...both replies are great!!!

[Rudi]

Or without an auxiliary column, you could use this array formula (confirm with Ctrl+Shift+Enter):

=SMALL(IF(MATCH(A1:A5,A1:A5,0)=ROW(A1:A5),A1:A5),3)

Hans,

It seems like this formula only works if the list of numbers is in a column starting at row 1. It produces a #NUM error if the list is any other position on the sheet.
Cheers

[HansV]

Correct. If the list starts elsewhere, let's say it's A10:A20, modify the formula as follows:

=SMALL(IF(MATCH(A10:A20,A10:A20,0)=ROW(A10:A20)-MIN(ROW(A10:A20))+1,A10:A20),3)

More general:

=SMALL(IF(MATCH(rng,rng,0)=ROW(rng)-MIN(ROW(rng))+1,rng),N)

where rng is the range of cells, and N is the position you want to return.

Don't forget to confirm with Ctrl+Shift+Enter.

Source: Formulas for Unique Data

[Rudi]

Perfect.
Cheers