Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,670
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 340
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,593
Default 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



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,670
Default 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




  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,593
Default 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








  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,670
Default Thank everyone very much for suggestions

Thank everyone very much for suggestions
Best Regards
Eric
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to determine the values from a list? Eric Excel Discussion (Misc queries) 9 March 24th 07 02:59 PM
How to determine the values from a list? Eric Excel Worksheet Functions 1 March 23rd 07 06:26 AM
How to determine second (and then third) highest value in a list Scott M. Lyon Excel Discussion (Misc queries) 2 September 12th 05 11:09 PM
How to determine second (and then third) highest value in a list Scott M. Lyon Excel Discussion (Misc queries) 4 September 12th 05 08:51 PM
List ? - How do I make information in one cell determine list to u Brad_A Excel Worksheet Functions 1 January 18th 05 05:10 PM


All times are GMT +1. The time now is 10:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"