1st smallest, 2nd smallest,3rd smallest

[checkQ]

I have a list of numbers in A1 to A5
4
4
5
8
10
I am trying to find the 1st smallest, 2nd smallest,3rd smallest etc number.
Using the formula =SMALL(A1:A5,2), the second smallest returns a 4 but I
really want it to return 5. I want to ignore duplicates. I tried RANK since
RANK ignores duplicates but it only returns the cell position. Does anyone
knows how to solve this enigma?
Thanks in advance.

 

[Spicelon]

[snip]

I tried RANK since
RANK ignores duplicates but it only returns the cell position. Does anyone
knows how to solve this enigma?
Thanks in advance.

??

Rank returns a number.

 

[Spicelon]

Disregard my reply. I'm still not quite awake.

 

[checkQ]

When I use =RANK(5,A1:A5,1) the answer I get is 3. which is the third
POSITION not the value. ( I expect an answer of 2 because 5 is the second
smallest number while 4 is the first ). But since there are two instances of
4 the formula assumes that the two fours make up for the 1st and second
smallest number so that 5 is the third smallest.

 

[Gary''s Student]

SMALL() is getting fooled by the duplicates. So let's get rid of them:

In B1 enter 1
In B2 enter:
=IF(A2="",-1,IF(COUNTIF($A$1:$A1,A2)>0,0,MAX($B$1:B1)+1))
and copy down

In C1 enter:
=INDEX($A$1:$A$30,MATCH(ROW(),B$1:B$30,0),1)
and copy down

This is what we see:

4 1 4
4 0 5
5 2 8
8 3 10
10 4

Note that column C is just column A with all the duplicates removed. Now

=SMALL(C:C,1) shows 4
=SMALL(C:C,2) shows 5
=SMALL(C:C,3) shows 8

 

[T. Valko]

Try this:

Enter this formula in D1:

=MIN(A1:A5)

Enter this array formula** in D2 and copy down until you get blanks:

=IF(D1=MAX(A$1:A$5),"",MIN(IF(A$1:A$5>D1,A$1:A$5)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[checkQ]

Greaat!

 

[checkQ]

Thanks!

 

[T. Valko]

You're welcome!