1st, 2nd, 3rd placing with variables

BussellT

New Member
Joined
Nov 29, 2017
Messages
5
I need to place players 1st, 2nd, 3rd, etc. on a score sheet. 1 win = 2 points, Loss = 0
To break ties (Which are many) there is a shot differential (Shots For - Shots Against). There are 3 games w/ a total of 6 game points possible. The RANK does not work, because any team w/ 6 game points is automatically above anybody who lost a game (ie; 4 game points, and 2 game points to 0).
Shot differential is added to the game points to determine placement of the ties. Unfortunately, several of the teams who lost games have total points w/ shot differential higher than the teams who lost no games (ie; 6 gam points). I'm going insane trying to figure this out...RANK is out of the question. Any suggestions?
Player_Ranking.png
[/url][/IMG]
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Welcome to the Forum!

One possibility would be to rank on the basis of 1000*Points + Shot differential

i.e. so that Shot differential would be material to the calculation only in the event of a points tie.
 
Upvote 0
Assuming
Headers of table Totals (Win, Tie, Loss,...,Game Points) in A4:H4
Data in A5:H28

Try this formula in I5 copied down until I28
=COUNTIF(H$5:H$28,">"&H5)+COUNTIFS(H$5:H$28,H5,F$5:F$28,">"&F5)+COUNTIFS(H$5:H$28,H5,F$5:F$28,F5,G$5:G$28,">"&G5)+1

Hope this helps

M.
 
Upvote 0
Please, as indicated above by Macropod, try to follow the rules of the forum. The link he provided is enough, i have nothing more to say.

Anyway, i downloaded your file and noticed that the Totals table is in columns AJ:AQ - headers in rows 6 and 7 and data in rows 8:31.

Then try
AR8 copied down
=COUNTIF(AQ$8:AQ$31,">"&AQ8)+COUNTIFS(AQ$8:AQ$31,AQ8,AO$8:AO$31,">"&AO8)+COUNTIFS(AQ$8:AQ$31,AQ8,AO$8:AO$31,AO8,AP$8:AP$31,">"&AP8)+1

M.
 
Upvote 0
Thank you very much Marcelo. I apologize for the cross-posting...I'm new to the forum world. This will not happen again!

Tom
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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