ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   second minimum value (https://www.excelbanter.com/excel-discussion-misc-queries/17817-second-minimum-value.html)

Ken

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



Biff

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


.


Ken

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


.




Biff

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


.



.



All times are GMT +1. The time now is 05:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com