Finding the 2nd smallest number

**knighttrader** · 07-28-2008, 06:04 PM

I want to know the 2nd lowest number in the following list.
I have tried using SMALL(X:X,2) but it returns 14.00 because there a two number 14's. The answer I want is 14.50.

Many thanks

14.00
14.00
14.50
14.50
15.00
16.00
16.00
16.00
16.00
16.00
16.00
16.00
16.50
16.50
16.50
16.50
17.00
17.00
17.00
17.00
17.00
17.50
19.50
20.00

**shg** · 07-28-2008, 06:12 PM

Maybe =SMALL(A1:A24, COUNTIF(A1:A24, MIN(A1:A24)) + 1)

**Ron Coderre** · 07-28-2008, 06:40 PM

With
A1:A24 containing your list of values
and
B1: (the rank of the value to find.....eg 5)

If there will be NO blanks:
This ARRAY FORMULA returns the list value for that rank (ignoring duplicates):

C1: =SMALL(IF(ROW($A$1:$A$24)=MATCH($A$1:$A$24,$A$1:$A$24,0),$A$1:$A$24),B1)

If there may be blanks, try this ARRAY FORMULA:

C1: =SMALL(IF(ISNUMBER($A$1:$A$24),IF(ROW($A$1:$A$24)=
MATCH($A$1:$A$24,$A$1:$A$24,0),$A$1:$A$24)),B1)

Note: Commit array formulas with CTRL+SHIFT+ENTER, instead of just ENTER.

In the above example, and using your data, the 5th smallest value is 16.5.

**olasa** · 07-28-2008, 07:16 PM

Just 4fun

Here is one more alternative. It is short but it Only works in this example:

=SMALL(A1:A24,1+FREQUENCY(A1:A24,A1:A24))

Frequency is normally an Array but if Ctrl+Shift+Enter is omitted Excel will use the first number.
And since the list is sorted...it works - but just in this case (meaning it's not a reliable solution)

//Ola