2nd lowest number in an array

[Guest]

I have a simple array, just 1 column.
But various numbers are listed multiple times.
using =SMALL(ARRAY,2) or =SMALL(ARRAY,3) or =SMALL(ARRAY,4)
all yield the same number.
Obviously =SMALL will yield the 2nd number as in a sequence of all numbers
in the array, it does not yield the 2nd Smallest or Lowest number.

 

[Domenic]

Assuming that A2:A10 contains the data, try the following...

For the second lowest unique number...

=SMALL(IF(A2:A10<>"",IF(MATCH(A2:A10,A2:A10,0)=ROW(A2:A10)-ROW(A2)+1,A2:A
10)),2)

....confirmed with CONTROL+SHIFT+ENTER.

To return a list of unique numbers, lowest to highest...

B2:

=MIN(A2:A10)

B3, copied down:

=MIN(IF($A$2:$A$10>B2,$A$2:$A$10))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Trevor Shuttleworth]

I suggest you use an Advanced Filter to copy unique values (numbers) to
another column on the sheet (or another sheet) and then use
=SMALL(UnuiquArray,2) to get the second smallest value.

Regards

Trevor

 

[Roger Govier]

Hi Greg
One way
Assuming your data is in column A, create a helper column (I used column
B) with the formula
=IF(COUNTIF($A$1:A1,A1)>1,"",A1)
copy down column B for the extent of your data
In C1, enter
=SMALL(B:B<ROW())
Copy down column C and you will see the smallest, 2nd smallest etc.

 

[Guest]

One way would be to use a helper column, with your data in column A,
in B1 put =A1
in B2 put =IF(A2=A1,"",A2) and copy down........
in C1 put =SMALL(B:B,2)

hth
Vaya con Dios,
Chuck, CABGx3

 

[Bernie Deitrick]

Greg,

Use a second Array, with formulas like this to remove duplicates:

=IF(COUNTIF($A$1:A1,A1)=1,A1,"")

(copied down to match your first array), then do the SMALL on the second array.

HTH,
Bernie
MS Excel MVP

 

[Marc]

I always hate trying to work with complicated formulas, in the same amount
of time it takes to come up with a formula, then test to make sure it works,
why not just create a pivot table on your column of numbers?

Just put "count" in the body of the table, then you'll have a column of
unique values... then use SMALL on that column...