A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

second minimum value



 
 
Thread Tools Display Modes
  #1  
Old March 16th 05, 03:38 AM
Ken
external usenet poster
 
Posts: n/a
Default second minimum value

Hi,
I have a formula....=INDEX(A10:A20,MATCH(MIN
B10:B20),B10:B20,0)) which returns the name in column A that corresponds to
the minimum number in B10:B20.

I would like a formula to also give me the second least valued number in
B10:B20. And the third, fourth and fifth.

Also, what will happen if there are two numbers that are minimum and equal
to each other?

Thank you,
Ken


Ads
  #2  
Old March 16th 05, 05:45 AM
Biff
external usenet poster
 
Posts: n/a
Default

Hi!

To find the nth smallest without duplicates:

=INDEX(A10:A20,MATCH(SMALL(B10:B20,X),B10:B20,0))

Replace X with the nth value you're interested in.

OR, use a cell to hold the nth value:

A1 = 2 (or 3, or 5, or 9)

=INDEX(A10:A20,MATCH(SMALL(B10:B20,A1),B10:B20,0))

If there might be duplicates it gets a little more
complicated. The best way to handle this is to "break
ties" by using a rank formula:

In C10 enter this formula and copy down to C20:

=RANK(B10,$B$10:$B$20,2)+COUNTIF($B$10:B10,B10)-1

This will rank the first instance of a duplicate higher
(or lower depending on which REFERENCE argument you use in
the RANK function) than the next instance.

For example:

Tom 100
Sue 100

Tom would get ranked as 1 and Sue would get ranked as 2.

Then you can use the INDEX formula based on the rankings
in column C:

=INDEX(A10:A20,MATCH(SMALL(C10:C20,2),C10:C20,0))

Biff

>-----Original Message-----
>Hi,
>I have a formula....=INDEX(A10:A20,MATCH(MIN
>B10:B20),B10:B20,0)) which returns the name in column A

that corresponds to
>the minimum number in B10:B20.
>
>I would like a formula to also give me the second least

valued number in
>B10:B20. And the third, fourth and fifth.
>
>Also, what will happen if there are two numbers that are

minimum and equal
>to each other?
>
>Thank you,
>Ken
>
>
>.
>

  #3  
Old March 16th 05, 03:14 PM
Ken
external usenet poster
 
Posts: n/a
Default

Thank you VERY much. It's exactly what I needed.
Enjoy your day.
Ken

"Biff" > wrote in message
...
> Hi!
>
> To find the nth smallest without duplicates:
>
> =INDEX(A10:A20,MATCH(SMALL(B10:B20,X),B10:B20,0))
>
> Replace X with the nth value you're interested in.
>
> OR, use a cell to hold the nth value:
>
> A1 = 2 (or 3, or 5, or 9)
>
> =INDEX(A10:A20,MATCH(SMALL(B10:B20,A1),B10:B20,0))
>
> If there might be duplicates it gets a little more
> complicated. The best way to handle this is to "break
> ties" by using a rank formula:
>
> In C10 enter this formula and copy down to C20:
>
> =RANK(B10,$B$10:$B$20,2)+COUNTIF($B$10:B10,B10)-1
>
> This will rank the first instance of a duplicate higher
> (or lower depending on which REFERENCE argument you use in
> the RANK function) than the next instance.
>
> For example:
>
> Tom 100
> Sue 100
>
> Tom would get ranked as 1 and Sue would get ranked as 2.
>
> Then you can use the INDEX formula based on the rankings
> in column C:
>
> =INDEX(A10:A20,MATCH(SMALL(C10:C20,2),C10:C20,0))
>
> Biff
>
>>-----Original Message-----
>>Hi,
>>I have a formula....=INDEX(A10:A20,MATCH(MIN
>>B10:B20),B10:B20,0)) which returns the name in column A

> that corresponds to
>>the minimum number in B10:B20.
>>
>>I would like a formula to also give me the second least

> valued number in
>>B10:B20. And the third, fourth and fifth.
>>
>>Also, what will happen if there are two numbers that are

> minimum and equal
>>to each other?
>>
>>Thank you,
>>Ken
>>
>>
>>.
>>



  #4  
Old March 16th 05, 06:17 PM
Biff
external usenet poster
 
Posts: n/a
Default

You're welcome! Thanks for the feedback.

Biff

>-----Original Message-----
>Thank you VERY much. It's exactly what I needed.
>Enjoy your day.
>Ken
>
>"Biff" > wrote in message
...
>> Hi!
>>
>> To find the nth smallest without duplicates:
>>
>> =INDEX(A10:A20,MATCH(SMALL(B10:B20,X),B10:B20,0))
>>
>> Replace X with the nth value you're interested in.
>>
>> OR, use a cell to hold the nth value:
>>
>> A1 = 2 (or 3, or 5, or 9)
>>
>> =INDEX(A10:A20,MATCH(SMALL(B10:B20,A1),B10:B20,0))
>>
>> If there might be duplicates it gets a little more
>> complicated. The best way to handle this is to "break
>> ties" by using a rank formula:
>>
>> In C10 enter this formula and copy down to C20:
>>
>> =RANK(B10,$B$10:$B$20,2)+COUNTIF($B$10:B10,B10)-1
>>
>> This will rank the first instance of a duplicate higher
>> (or lower depending on which REFERENCE argument you use

in
>> the RANK function) than the next instance.
>>
>> For example:
>>
>> Tom 100
>> Sue 100
>>
>> Tom would get ranked as 1 and Sue would get ranked as 2.
>>
>> Then you can use the INDEX formula based on the rankings
>> in column C:
>>
>> =INDEX(A10:A20,MATCH(SMALL(C10:C20,2),C10:C20,0))
>>
>> Biff
>>
>>>-----Original Message-----
>>>Hi,
>>>I have a formula....=INDEX(A10:A20,MATCH(MIN
>>>B10:B20),B10:B20,0)) which returns the name in column A

>> that corresponds to
>>>the minimum number in B10:B20.
>>>
>>>I would like a formula to also give me the second least

>> valued number in
>>>B10:B20. And the third, fourth and fifth.
>>>
>>>Also, what will happen if there are two numbers that are

>> minimum and equal
>>>to each other?
>>>
>>>Thank you,
>>>Ken
>>>
>>>
>>>.
>>>

>
>
>.
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Finding minimum value across selected rows of an array Dazed and confused about min, max Excel Worksheet Functions 2 February 25th 05 11:11 PM
Hiding Values Below Z Axis Minimum Value on Axis Charts TryingToExcel Charts and Charting in Excel 1 February 18th 05 03:55 AM
How do I get "minimum value" in a range to NOT return zero? Blade Excel Worksheet Functions 4 February 2nd 05 02:06 AM
lookup - return minimum value Steve R Excel Worksheet Functions 7 December 30th 04 04:44 AM
Sum minimum values in a pivot table AK Excel Worksheet Functions 1 December 22nd 04 08:55 PM


All times are GMT +1. The time now is 08:50 AM.


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