Song competition - ranking order and breaking ties

I think I've managed to break the ties but it's not an overly efficient solution as it still requires a helper column. There are probably much better solutions out there but this one should still work.

Hi, many thanks for this, much appreciated! I don't mind about the helper column. I suppose for the final version it could be hidden?

I think it's almost there but there's still one unresolved tie between song 8 and song 12, both with 22 points. They both generate a figure of 22.71 in the helper column, but song 12 should be slightly higher than song 8 to generate 4th and 5th places. At the moment they're both at 4th place. I've looked at your formula and I can't spot why it doesn't work for those particular songs but it does for all the rest. Intriguing!

The reason they are both tied is because I put in 2 tie-breaker criteria: Highest single vote, and number of times they received that vote. As they both have the same total, the same highest vote number, and the same frequency for that highest vote number, they show the same value. It basically needs more tie-breaker criteria.

Hi

Hope this will help you

Click on attachment

Thanks micope21, I appreciate you taking the time to help, unfortunately it doesn't quite work!

The scores generated by the helper column don't always correlate to the level of the actual score eg. ranks 1 and 2 should be the other way round and place 6 should be taken by artist 2 with 18 points comprising three '5' scores. Artist 4 with 17 points is incorrectly ranked too.

Hmm, this is trickier than I thought! Thanks again for the replies though.

It's not really that much trickier, you just need to define more tie-break criteria, as your current criteria still leads to ties.

Thanks Down Under.

I understand your logic. For it to work in spreadsheet terms we'd need more mathematical criteria to break further ties and I'm not sure if that's possible when we're only looking at four scores per song.

Your method broke all the ties bar one, so that's a significant improvement over what we had. If we were just left with one or two ties we could break those subjectively on the day by asking the judges which song they wanted to go through.

This should work this time.

Never say impossible. There are many formula to work on tied breaker. It take time working on till you got it.

Thank you micope21 - this does work!

I'll run more tests just to make sure but I think you've cracked it.

Just one thing - if I have an extra judge, which might happen sometimes, how do I amend the ranking formula? I can see that I'd add another column on the right (column P) to include the fifth number:

=LARGE($D2:$G2,5)

Current ranking formula:

=H2 + (L2/100) + (M2/1000) + (N2/100000) + (O2/1000000) +(ROW()/100000000)

Would I simply add (P2/10000000) to the above?

Thanks again.

=H2 + (L2/100) + (M2/1000) + (N2/100000) + (O2/1000000) + (P2/10000000) + (ROW()/1000000000)

Also instead =Large($D2:$G2,1) so on 2, 3. Put in =LARGE($D2:$G2,COLUMN(A1)) Copy across and down.

Here other way of tied break sample file.
Info are in the file.

Cheers

Thanks micope21. This all works fine.

Your second method using COUNTIFS and a range between 4 and 10 is also good. I wonder which will be the most reliable? I guess only time will tell!

Cheers for all the advice.

You welcome. Yes time will tell which is better till you happy with it. If you happy with it.

Please Marking threads "Solved" . Select Thread Tools (on top of your 1st post) -> Mark this thread as Solved. then save.

Cheers