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.

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$10B2,$A$2:$A$10))

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

Hope this helps!

In article ,
Greg wrote:

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.

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


"Greg" wrote in message
...
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.

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.

--
Regards

Roger Govier


"Greg" wrote in message
...
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.

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



"Greg" wrote:

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.

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


"Greg" wrote in message
...
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.

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

"Greg" wrote in message
...
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.