1st, 2nd, 3rd placing with variables

[BussellT]

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?

[/url][/IMG]

 

[BussellT]

Well ... the URL Failed. I'll see if this works:

 

[StephenCrump]

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.

 

[Marcelo Branco]

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.

 

[Macropod]

Cross-posted at: https://www.excelforum.com/excel-formulas-and-functions/1210629-placing-players-1st-2nd-3rd-etc.html
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[Marcelo Branco]

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.

 

[BussellT]

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

Tom

 

[Marcelo Branco]

Tom

No problem. Hope the formula has worked for you.

Regards

M.