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. 


Thread Tools  Display Modes 
#1




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




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




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




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  


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 26th 05 12:11 AM 
Hiding Values Below Z Axis Minimum Value on Axis Charts  TryingToExcel  Charts and Charting in Excel  1  February 18th 05 04:55 AM 
How do I get "minimum value" in a range to NOT return zero?  Blade  Excel Worksheet Functions  4  February 2nd 05 03:06 AM 
lookup  return minimum value  Steve R  Excel Worksheet Functions  7  December 30th 04 05:44 AM 
Sum minimum values in a pivot table  AK  Excel Worksheet Functions  1  December 22nd 04 09:55 PM 