Home |
Search |
Today's Posts |
#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 |
#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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding minimum value across selected rows of an array | Excel Worksheet Functions | |||
Hiding Values Below Z Axis Minimum Value on Axis Charts | Charts and Charting in Excel | |||
How do I get "minimum value" in a range to NOT return zero? | Excel Worksheet Functions | |||
lookup - return minimum value | Excel Worksheet Functions | |||
Sum minimum values in a pivot table | Excel Worksheet Functions |