Calculate Score based on time with ties

[Bjorn_NZ]

Hello all,

I have been working on a scoring sheet for my Crossfit Gym which will be hosting a competition in the new future. So far so good I just am not sure how to incorporate time ties to work out placing.

E.g. if Team 1 and Team 2 both finish in exactly 5:00 minutes the formula just accepts the latest entry as the highest score instead of calculating the tie. When it comes to placing based on score I think the RANK formula takes care of it - just not when it comes to time.

Appreciate any help I can get!
Thanks in advance!

Thanks
Bjorn

[MarvinP]

Hi Bjorn and welcome to the forum,

There is a thing called the scorecard playoff in golf that breaks ties. If two players are tied after 18 holes it then looks at the last 9 holes to break the tie and if still tied then last 6 then 3 then 1. I'm wondering if you can do a similar thing with your scores. You would keep a total of places as the final value. Then divide the sum or the last few events by 100 and add it to the total and then the last event divided by 10000 and add it too.

So if a team got Places of 3, 4, 2 then there score would be 9.0602. This might make no logic at all but might also work for you. The 06 is from the sum of the last 2 scores and the 2 is from the last score.

Hope that gives you an idea....

[Bjorn_NZ]

Hi Marvin

Thanks for the reply although I don't think that would work in this case.

2 of the events are time based like running a marathon or a 100m dash, therefore if Team 1 and Team 2 both finish at 5 minutes they would need to share the same placing e.g. 1st and then the next team to finish would have placing 3.

If I am just using RANK to calculate placing based on score(=RANK(F2,F$2:F$11)) it works fine.

When I am calculating placing based on time e.g. (=COUNTA(B$2:B$11)+2-RANK(B2,B$2:B$11)-COUNTIF(B$2:B2,B2)) is where the issue comes in.

Sorry if I'm not being detailed enough.

[MarvinP]

Hi Bjorn,

Another method is to convert all event scores to Student T scores. Ranks are not very exact. The idea in Student T scores to to let the average score be 50 with 10 as the standard deviation of the group of scores. It is a valid operation to average these scores. It is not a valid operation to average percents or ranks.

http://www.stattrek.com/probability-...tribution.aspx
http://edtech.kennesaw.edu/dataanaly...testscores.htm

[Bjorn_NZ]

Thanks Marvin

I will try some of those out. Didn;t think it would need to be so complicated though

[MarvinP]

Hi Bjorn,

I've found that when a tiebreaker is needed, there will always be an unhappy person/team. A very important part of the process is to have it figured out before it is needed. Let the computer do the math work and announce the winner. Then explain to the loser what the rules were for the tiebreaker. If you can't explain it or the loser thinks the rules are stupid, at least you didn't play favorites, based on who they were.

Better yet is to announce to everyone how a tie is handled before it is needed. That way they can understand what is happening before it is needed.

[Bjorn_NZ]

Thanks Marvin, a good suggestion. For my situation I think RANK will work just fine. There won't be many teams so everything can easily be verified manually.

Appreciate your help!

[Cutter]

@ Bjorn_NZ

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.