Ranking and not showing ties

A

astrophys

New Member
Joined
Apr 21, 2017
Messages
14
I'm using this formula in Column A =IF(G3="","",IF(G3=G2,A2,ROW()-2)), which will show a tie for the same rank (i.e. Rank 1 [Bell and Brown] and Rank 4 for both Beckham and Elliott). What would be the formula to rank them 1-5? I would prefer not to show a tie.

Thank you in advance!

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column G[/TD]
[/TR]
[TR]
[TD]Rank[/TD]
[TD]Name[/TD]
[TD]AVG[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Brown[/TD]
[TD]1.67[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Bell[/TD]
[TD]1.67[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Evans[/TD]
[TD]2.00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Beckham[/TD]
[TD]2.33[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Elliot[/TD]
[TD]2.33[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]RANK[/TD]
[TD]NAME[/TD]
[TD]Ranker1[/TD]
[TD]Ranker2[/TD]
[TD]Ranker3[/TD]
[TD]Ranker4[/TD]
[TD]AVG[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Brown[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Bell[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Evans[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Elliot[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Johnson[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
This works! However, in columns C-F (not shown) I have a list of rankers where they rank the information how they see fit and their AVG is calculated out in Column G. Some rankers may rank better than the average rank (Ranker1 thinks Elliot should be 3 and the RANK is actually 4). How would I formulate the names to where is stays ranked off the average? For example, consider 4 rankers and the Rank column has already been averaged from lowest to highest using the AVG column. Let's say I filter by Ranker1, I would like the Rank column to stay ranked from the AVG column. So, Evans and Elliot would switch. Hope this makes sense! :/
 
Upvote 0
I'm not sure if I understand what you're looking for. See if this does what you want
Transpose the table, that is: Rankers in column A and Names in row 1.
Something like this...


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
NAME​
[/TD]
[TD]
Brown​
[/TD]
[TD]
Bell​
[/TD]
[TD]
Evans​
[/TD]
[TD]
Elliot​
[/TD]
[TD]
Johnson​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
Ranker1​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
4​
[/TD]
[TD]
3​
[/TD]
[TD]
5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
Ranker2​
[/TD]
[TD]
2​
[/TD]
[TD]
3​
[/TD]
[TD]
1​
[/TD]
[TD]
4​
[/TD]
[TD]
5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
Ranker3​
[/TD]
[TD]
5​
[/TD]
[TD]
4​
[/TD]
[TD]
3​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
Ranker4​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
3​
[/TD]
[TD]
4​
[/TD]
[TD]
5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
AVG​
[/TD]
[TD]
2,25​
[/TD]
[TD]
2,75​
[/TD]
[TD]
2,75​
[/TD]
[TD]
3​
[/TD]
[TD]
4,25​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
RANK​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
3​
[/TD]
[TD]
4​
[/TD]
[TD]
5​
[/TD]
[/TR]
</tbody>[/TABLE]


Formula in B7 copied across
=SUBTOTAL(101,B2:B5)

Formula in B8 copied across
=COUNTIF($B7:$F7,"<"&B7)+COUNTIF($B7:B7,B7)

Apply filter, criteria = Ranker1, and...

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
NAME​
[/TD]
[TD]
Brown​
[/TD]
[TD]
Bell​
[/TD]
[TD]
Evans​
[/TD]
[TD]
Elliot​
[/TD]
[TD]
Johnson​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
Ranker1​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
4​
[/TD]
[TD]
3​
[/TD]
[TD]
5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
AVG​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
4​
[/TD]
[TD]
3​
[/TD]
[TD]
5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
RANK​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
4​
[/TD]
[TD]
3​
[/TD]
[TD]
5​
[/TD]
[/TR]
</tbody>[/TABLE]


M.
 
Upvote 0
I'm also unsure what you're asking, but here are a couple of ideas:

ABCDEFGHIJKLM
Ranker3Beckham
Elliot
Evans
Bell
Brown

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Rank[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Ranker1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Ranker2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Ranker3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Ranker4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]AVG[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Adj. Avg.[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Tie-breaking Ranker[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Order[/TD]
[TD="align: right"]Order by Ranker[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Brown[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1.67[/TD]
[TD="align: right"]1.675[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Bell[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1.67[/TD]
[TD="align: right"]1.674[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Evans[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="align: right"]2.003[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Beckham[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2.33[/TD]
[TD="align: right"]2.331[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Elliot[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2.33[/TD]
[TD="align: right"]2.332[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A3[/TH]
[TD="align: left"]=RANK(G3+INDEX(C3:F3,MATCH($J$3,$C$1:$F$1,0))/1000,$H$3:$H$7,1)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H3[/TH]
[TD="align: left"]=G3+INDEX(C3:F3,MATCH($J$3,$C$1:$F$1,0))/1000[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L3[/TH]
[TD="align: left"]=ROW()-2[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M3[/TH]
[TD="align: left"]=INDEX($B$3:$B$7,MATCH(L3,OFFSET($C$3:$C$7,0,MATCH($J$3,$C$1:$F$1,0)-1),0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



If you want the players to be ranked in order by AVG, but in case of a tie, use a given Ranker as the tie-breaker: Put the formula in H3 and drag down. Put the formula in A3 and drag down. Put the ranker you want to be the tie-breaker in J3. In this example, Bell and Brown are still 1 and 2, but they switch position because Ranker3 ranked Bell higher.

If you just want a list of the players by ranker, put the formulas in L3:M3 and drag down.

If want you want isn't covered by Marcelo's or my ideas, it would help if you could show an example of what you're looking for.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Return and rank top 3 results (including ties) from VSTACK, FILTER, CHOOSECOLS, and SORT formula
Replies
8
Views
207
Peter_SSs
Peter_SSs
JenniferMurphy
  • Solved
How to calculate rank (subrank?) in NFL conference and division?
Replies
6
Views
97
JenniferMurphy
JenniferMurphy
E
  • Solved
Ranking Invoices By Highest Amount By "Year to Date"
Replies
2
Views
159
ellison
E
N
  • Question Question
rank function tie breaker
Replies
3
Views
634
Fluff
Fluff
S
  • Solved
How to work out general trend in a series of numbers (film rankings)
Replies
5
Views
468
stefanaalten
S

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top