Help with ranking using the LARGE function

[marquez313]

Hi,

I'm using the LARGE function to rank results of daily stock moves. The problem I'm having is when 2 or more stocks have the same value. For example, if 3 of the top ranking stocks all have a value of $5.00, all three values will be listed, but the match for the values will only be the first $5.00 value.

In other words, what I want is:
1 IBM $5.00
2 AFL $5.00
3 BUD $5.00

What I get is:
1 IBM $5.00
2 IBM $5.00
3 IBM $5.00

Thanks in advance for your help!

 

[Andrew Poulsom]

If your data is in the range A1:B3, in C1 type:

=INDEX(A$1:A$3,MATCH(LARGE((B$1:B$3+(ROW(A$1:A$3)/1000000)),ROWS(A$1:A1)),(B$1:B$3+(ROW(A$1:A$3)/1000000)),FALSE))

and confirm with Ctrl+Shift+Enter. Copy that formula to C2:C3.

 

[T. Valko]

Hi,

I'm using the LARGE function to rank results of daily stock moves. The problem I'm having is when 2 or more stocks have the same value. For example, if 3 of the top ranking stocks all have a value of $5.00, all three values will be listed, but the match for the values will only be the first $5.00 value.

In other words, what I want is:
1 IBM $5.00
2 AFL $5.00
3 BUD $5.00

What I get is:
1 IBM $5.00
2 IBM $5.00
3 IBM $5.00

Thanks in advance for your help!

You can use a rank method that breaks ties and results in a unique rank for each value then you can do your lookup based on the unique ranks. For example...

<TABLE style="WIDTH: 117pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=156 border=0 x:str><COLGROUP><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 1760" width=55><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 1664" width=52><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1568" width=49><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 41pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=55 height=17>Ticker</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 39pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=52>Price</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 37pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=49>Rank</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>IBM</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:str="'$5.00 ">$5.00 </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">1 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>AFL</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:str="'$5.00 ">$5.00 </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>BUD</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:str="'$5.00 ">$5.00 </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">3 </TD></TR></TBODY></TABLE>

The rank formula entered in C2 is:

=RANK(B2,B$2:B$4)+COUNTIF(B$2:B2,B2)-1

Copy down as needed.