Rank with a 4 tie break criteria

EVANS8773

New Member
Joined
Nov 3, 2017
Messages
4
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

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