Ranking after a shoot-off

[Brian]

Pos Name Score Shoot-off Total New Pos
1 Black B 100 20 120 2
2 Henderson R 100 25 125 1
3 Smith J 99 3
4 Jones H 98 23 121 5
5 Rossouw N 98 24 122 4
6 Harington D 96 6
7 Ieul P 94 23 117 7
8 Tait M 94 21 115 9
9 Els N 94 22 116 8
10 Murray R 92 10

The table above is used for a competition. Only those with tied
scores compete in a shoot-off. I need the formula for the last
column to determine the final ranking. It is important to note that
where position 1 and 2 have the same score, after the shoot-off they
can only be re-ranked in position 1 or 2 irrespective of the fact that
those in positions 4 and 5 have a higher score after the shoot-off.
The same applies with other tied scores.

 

[Pete_UK]

If instead of treating the shoot-off score as whole numbers, you
divided that score by 100 and then added that to the previous score,
this will solve your problem, i.e. you would have 100.2 and 100.25 for
your first two, and 98.23 and 98.24 for 4th/5th ties etc. As long as
no-one can score more than 100 in the shoot-off then the results will
not affect the ranking of those with no ties (eg 3rd place in your
example).

Hope this helps.

Pete

 

[Shane Devenshire]

Hi,

In the Total column use the formula in E2:

=C2+D2/100*COUNTIF($C$2:$C$11,C2)

which assumes titles on row 1 and POS starting in column A. Enter the
following formula in the New Pos column

=RANK(E2,$E$2:$E$11,0)

 

[Brian]

Hi,

In the Total column use the formula in E2:

=C2+D2/100*COUNTIF($C$2:$C$11,C2)

which assumes titles on row 1 and POS starting in column A.  Enter the
following formula in the New Pos column

=RANK(E2,$E$2:$E$11,0)
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire






- Show quoted text -

Thank you this works perfectly