scoring system - handle ties

[jm14638]

I've written a small scoring system which lets the user enter team name
and scores on one sheet and displays the event results on the secon
sheet. I've got 2 levels of tie-breaking logic before I declare th
tie 'unbreakable'. In the case of an 'unbreakable' tie, there will b
at least 2 teams with the same final score.

Example of 3 way tie:

Team 1 98
Team 2 94
Team 3 98
Team 4 85
Team 5 98

When there isn't a tie I can just look for the team on the same row a
the n-th largest score and rank them appropriately. When there is
tie however, my logic returns the team name from the first row it find
with that score... in the above example my results would look like:

Team 1 98
Team 1 98
Team 1 98
Team 2 94
Team 4 85

How can I get it to return all three team names instead of repeatin
just team 1

 

[Frank Kabel]

Hi
try the following formula (entered in row 1 as array formula with
CTRL+SHIFT+ENTER):
=INDEX($A$1:$A$5,MATCH(TRUE,$B$1:$B$5+ROW($B$1:$B$5)/1000=LARGE($B$1:$B
$5+ROW($B$1:$B$5)/1000,ROW()),0))

and copy this down to get the team names (with or without ties).
Assumptions:
- column A contains the team names
- column B contains the scores
- this formula is entered in row 1

For getting the corresponding scores in the adjacent column use
=INDEX($B$1:$B$5,MATCH(TRUE,$B$1:$B$5+ROW($B$1:$B$5)/1000=LARGE($B$1:$B
$5+ROW($B$1:$B$5)/1000,ROW()),0))

 

[jm14638]

I can't say I totally understand how it works... but that solved it!

Thanks so much for your help

 

[jm14638]

Back again... this time it's my tie breaker logic. Bear with me while
give a little background... My score system actually accepts 5 score
for each team. The high and low scores are dropped and the sum of th
'middle 3' scores determine a teams total score. These total score
determine a teams rank.

If there is a tie, I add back in the high score for each team an
re-rank the teams.

If there is still a tie, I add back in the low score for each team an
re-rank the teams again.

If there is still a tie, I declare the tie 'unbreakable' and rever
back to the original scores.

My problem is best explained by an example... Say Team A was in 1s
place with a score of 90 in the original rankings and wasn't tied wit
anyone. Team B and C were tied for 2nd place with scores of 80. Afte
adding back in the high scores, Team

Team A 20 30 30 30 40
Team B 10 20 30 30 70
Team C 10 20 30 30 30

Original Scores:
Team A(150 - 40 - 20 = 90)
Team B(160 - 70 -10 = 80)
Team C(120 - 30 - 10 = 80)

After 1st tie breaker

 

[Frank Kabel]

Hi
you may have a look at the following site
http://www.cpearson.com/excel/rank.htm