Tags: a10a20, b10b20, column, corresponds, excel, formula, index, match, microsoft, min, minimum, msdn, number, returns, second, software, value

second minimum value

On Microsoft » Microsoft Excel

5,804 words with 3 Comments; publish: Thu, 22 May 2008 21:51:00 GMT; (30678.13, « »)

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

All Comments

Leave a comment...

  • 3 Comments
    • 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

      >

      >.

      >

      #1; Thu, 22 May 2008 21:52:00 GMT
    • Thank you VERY much. It's exactly what I needed.

      Enjoy your day.

      Ken

      "Biff" <biffinpitt.excel.questionfor.info.comcast.net> wrote in message

      news:0a7701c529eb$637eb9b0$a601280a.excel.questionfor.info.phx.gbl...

      > 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

      >>

      >>.

      #2; Thu, 22 May 2008 21:53:00 GMT
    • 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" <biffinpitt.excel.questionfor.info.comcast.net> wrote in message

      >news:0a7701c529eb$637eb9b0$a601280a.excel.questionfor.info.phx.gbl...

      >> 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; Thu, 22 May 2008 21:54:00 GMT