How to determine the value from a list?
 

Does anyone have any suggestions on how to determine the value from a list?

22,22,25,27,27,28

I would like to determine the second smallest value, but skip checking the
repeated one, such as in this case, I should select 25 and skip 22 because
there are repeated 22 from the list.
Does anyone have any suggestions?
Thank you in advance
Eric

How to determine the value from a list?
 

Try array-entered (ctrl+shift+enter to evaluate):

=MIN(IF(A1:A6MIN(A1:A6),A1:A6))

On 22 May, 08:25, Eric wrote:
Does anyone have any suggestions on how to determine the value from a list?

22,22,25,27,27,28

I would like to determine the second smallest value, but skip checking the
repeated one, such as in this case, I should select 25 and skip 22 because
there are repeated 22 from the list.
Does anyone have any suggestions?
Thank you in advance
Eric

How to determine the value from a list?
 

Assuming the numbers are in M1:M6

=MIN(IF(M2:M6<M1:M5,M2:M6))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Eric" wrote in message
...
Does anyone have any suggestions on how to determine the value from a
list?

22,22,25,27,27,28

I would like to determine the second smallest value, but skip checking the
repeated one, such as in this case, I should select 25 and skip 22 because
there are repeated 22 from the list.
Does anyone have any suggestions?
Thank you in advance
Eric

How to determine the value from a list?
 

Thank everyone for suggestions

22,22,25,27,27,28,28,31,34,34,37

My intention is to list out all the numbers in ascending order without
duplication.
Could anyone give me more suggestions please?
Thank everyone
Eric



"Bob Phillips" wrote:

Assuming the numbers are in M1:M6

=MIN(IF(M2:M6<M1:M5,M2:M6))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Eric" wrote in message
...
Does anyone have any suggestions on how to determine the value from a
list?

22,22,25,27,27,28

I would like to determine the second smallest value, but skip checking the
repeated one, such as in this case, I should select 25 and skip 22 because
there are repeated 22 from the list.
Does anyone have any suggestions?
Thank you in advance
Eric

How to determine the value from a list?
 

That's completely different to what you asked.

Assuming the numbers are in A1:A10.

In B1: =A1

In B2 enter this formula and copy down

=IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""), 0)),"",
INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH( 0,COUNTIF(B$1:B1,$A$1:$A$20&""),0)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Eric" wrote in message
...
Thank everyone for suggestions

22,22,25,27,27,28,28,31,34,34,37

My intention is to list out all the numbers in ascending order without
duplication.
Could anyone give me more suggestions please?
Thank everyone
Eric



"Bob Phillips" wrote:

Assuming the numbers are in M1:M6

=MIN(IF(M2:M6<M1:M5,M2:M6))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets),
do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Eric" wrote in message
...
Does anyone have any suggestions on how to determine the value from a
list?

22,22,25,27,27,28

I would like to determine the second smallest value, but skip checking
the
repeated one, such as in this case, I should select 25 and skip 22
because
there are repeated 22 from the list.
Does anyone have any suggestions?
Thank you in advance
Eric

Thank everyone very much for suggestions
 

Thank everyone very much for suggestions
Best Regards
Eric

How to determine the value from a list?
 

Here's an alternative. This will extract the unique numbers in ascending
order.

Numbers in the named range RNG. RNG = A2:A11
D1 = column header (or empty or just not a number that appears in RNG)

Entered in D2 and copied down until you get blanks:

=IF(ROWS($1:1)<=COUNT(1/FREQUENCY(rng,rng)),SMALL(rng,SUMPRODUCT(COUNTIF(r ng,D$1:D1))+1),"")

Biff

"Bob Phillips" wrote in message
...
That's completely different to what you asked.

Assuming the numbers are in A1:A10.

In B1: =A1

In B2 enter this formula and copy down

=IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""), 0)),"",
INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH( 0,COUNTIF(B$1:B1,$A$1:$A$20&""),0)))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not just Enter.
Excel will automatically enclose the formula in braces (curly brackets),
do not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Eric" wrote in message
...
Thank everyone for suggestions

22,22,25,27,27,28,28,31,34,34,37

My intention is to list out all the numbers in ascending order without
duplication.
Could anyone give me more suggestions please?
Thank everyone
Eric



"Bob Phillips" wrote:

Assuming the numbers are in M1:M6

=MIN(IF(M2:M6<M1:M5,M2:M6))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets),
do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Eric" wrote in message
...
Does anyone have any suggestions on how to determine the value from a
list?

22,22,25,27,27,28

I would like to determine the second smallest value, but skip checking
the
repeated one, such as in this case, I should select 25 and skip 22
because
there are repeated 22 from the list.
Does anyone have any suggestions?
Thank you in advance
Eric