Rank with a 4 tie break criteria

[EVANS8773]

Hi all, this is my first post so please go easy

I have a Speedway grand prix sheet i have put together with basic formulas. Not happy with that i have tried to go a bit further and have now gotten out of my comfort zone.

As u can see from the sheet the AC column totals up the riders score from the races of which there are 20.

As an example in race 1 Red won so scores 3, white 2nd scores 2, yellow 3rd scores 1 and blue last scores 0.

After 20 races the top 8 go into 2 semi finals. Column AD ranks these scores in AC and in this sheet riders 5 (Hancock) and 7 (Penhall) both have 10 points and are both ranked 3rd.

Now the tricky bit. (deep breath)

Column A3 totals race wins 3pts (criteria 1), AF 2nd places 2 points (criteria 2) and AG 3rd places 1 point (criteria 3).

In a tie like this most race wins (3 points) comes needs to be higher in the rank then 2nd places is used then 3rd...In the example they both have 1 1st place, 3 second places and 1 3rd place..so are equally ranked

So now it needs to go down to the race (they only race eachother once) were they met which was race 2 and Penhall outscores Hancock.

So Penhall needs to outrank Hancock...

so in short the criteria is

most race wins (3 points)
most 2nd places (2 points)
most 3 rd places ( 1 point)
the race in which they face each other, race 2 in this example.

Got a headache now, so over to you brainy lot..

Thnaks for any assistance you can offer.

screenshot link below

https://www.dropbox.com/s/ozqedn0t7f5j9ot/Screenshot 2017-11-03 12.36.51.png?dl=0

If anyone would like the document to play with just email me evans8773@gmail.com

Cheers

 

[DRSteele]

Welcome to the forum.

I do not have a complete solution for you. But I can tell you then when trying to use tie-breakers in ranking things like teams, the best approach is to use helper columns. The columns relate to each successive tie-breaker and are arranged in decreasing order of importance. So for the NHL (which I experiment on), the ranking scenario goes like this, including the first three two breakers: 1) Points, 2) Fewer games played (which amounts to Pts%), 3) Wins excluding Shoot-out wins.

The fist step is to create a new measure (call it Grade for example) that incorporates each tie-breaker in decreasing order of magnitude. Then add all the tie-breaker scenarios together. See Post#2 in this Thread (and the linked Thread I quoted) so that I don't have to type it all out again. https://www.mrexcel.com/forum/excel...ing-multiple-tiebreakers.html?highlight=grade

The problem in the NHL scenario is that when two or more teams are still tied after Pts% and ROW, the next step is comparing their records in games between and amongst them, which is a royal pain in itself; then, the extra home game gets omitted, which is too hard for my poor brain to try to do in Excel, try as I might. It can't just be done with a summary table like the standings, which is too bad; it sounds like your scenario has the same limitation.

 

[EVANS8773]

Hi thanks for the response. I can work out what the rank should be visually in seconds but I want the pc to do it. ?

The 3 criteria 1st places, 2nd places and 3rd places is ok but the head to head race is virtually impossible. I will look at what you suggest in the morning accompanied by YouTube tutorials.

Your help is much appreciated


Thanks