Ranking Lists

[Gwyndalf]

To award prize money against a ranking list it's easy if there are no tied
players - ie 1st 2nd 3rd 4th. If 2 people tie for 2nd -ie 1st 2nd 2nd 4th.
If 1st gets 300, 2nd 200 3rd 100 4th 50. How can I automate a ranking list
to compensate so that in the event of a tie (2-way, or 3 way even) that the
total prize money would not be exceded?
Ideally 1st still gets 300 but the 2 2nd places each get (200+100)/2=150. If
a 3-way tie for 2nd - (200+100+50)/3= 116(ish). This is part of a programme
which will list upwards of 20 - all of which will qual for some reward except
last place. Any suggestions?

 

[Joel]

Prize Table

Col A Col B
Rank Dollar Amount

1 10000
2 9000
3 8000
4 7000
5 6000
6 5000
7 4000
8 3000
9 2000
10 1000



Col F Col G Col H
Player Rank Prize

John 1 9500
Joe 1 9500
Mary 3 8000
Carol 4 7000
Bob 5 5500
Amy 5 5500
Ted 7 3000
Mark 7 3000
Steve 7 3000
Dave 10 1000

Put the following formula in H1 and copy down column

=AVERAGE(OFFSET(B$1,MATCH(G1,A$1:A$10)-1,0,COUNTIF(G$1:G$10,G1),1))

 

[Pete_UK]

You need to list your table of prizes somewhere - I've assumed this
table:

1 300
2 200
3 100
4 50
5 20
6 0

is for 6 players and occupies I1:J6.

Then, with your rankings in A1:A6, put this formula in B1:

=(VLOOKUP(A1,$I$1:$J$6,2)+VLOOKUP(A1+1,$I$1:$J$6,2)*(COUNTIF(A:A,A1)

=2)+VLOOKUP(A1+2,$I$1:$J$6,2)*(COUNTIF(A:A,A1)>=3)+VLOOKUP(A1+3,$I

$1:$J$6,2)*(COUNTIF(A:A,A1)>=4))/COUNTIF(A:A,A1)

and copy down to B6. This will accommodate up to 4 ties.

Hope this helps.

Pete

 

[Gwyndalf]

vg soln ty

Prize Table

Col A Col B
Rank Dollar Amount

1 10000
2 9000
3 8000
4 7000
5 6000
6 5000
7 4000
8 3000
9 2000
10 1000



Col F Col G Col H
Player Rank Prize

John 1 9500
Joe 1 9500
Mary 3 8000
Carol 4 7000
Bob 5 5500
Amy 5 5500
Ted 7 3000
Mark 7 3000
Steve 7 3000
Dave 10 1000

Put the following formula in H1 and copy down column

=AVERAGE(OFFSET(B$1,MATCH(G1,A$1:A$10)-1,0,COUNTIF(G$1:G$10,G1),1))