2nd lowest number in an array
 

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.

2nd lowest number in an array
 

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.

2nd lowest number in an array
 

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.

2nd lowest number in an array
 

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.

2nd lowest number in an array
 

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.

2nd lowest number in an array
 

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.

2nd lowest number in an array
 

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.