Ranking Lists

G

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?
 
J

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))
 
P

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
 
G

Gwyndalf

vg soln ty

Joel said:
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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Percent Help 2
cascading ranking system 1
Excel Formula's 5
How to set the day date in excel to display 1st, 2nd, etc...? 2
nested "If" fuction 4
Show Ranking 1
max scores 2
Ranking problem 8

Top