1st smallest, 2nd smallest,3rd smallest

C

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

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

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

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

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top