Split the prize amounts for ties?

[Douglas Edward]

How do I create a formula to split the prize amounts for ties? (It's for a golf league)

Player Score Rank Prize Rank Prize In this example the prize payout should be…
A 70 1 ? 1 100 95
B 70 1 ? 2 90 95
C 71 3 ? 3 80 70
E 71 3 ? 5 60 70
F 72 6 ? 6 50 30
G 72 6 ? 7 40 30
H 72 6 ? 8 30 30
I 72 6 ? 9 20 30
J 72 6 ? 10 10 30

 

[kvsrinivasamurthy]

[TABLE="class: grid, width: 384"]
<tbody>[TR]
[TD="width: 64"]Player[/TD]
[TD="width: 64"]score[/TD]
[TD="width: 64"]rank[/TD]
[TD="width: 64"]prize[/TD]
[TD="width: 64"]rank[/TD]
[TD="width: 64"]prize[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]K[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]

Formula in C2

=RANK(B2,$B$2:$B$11,1)

Formula in D2

=SUMPRODUCT(($F$2:$F$11)*($E$2:$E$11>=C2)*($E$2:$E$11<(C2+COUNTIF($C$2:$C$11,C2))))/COUNTIF($C$2:$C$11,C2)

Both formulas dragged down.

 

[Douglas Edward]

That works perfectly! Thank you Marcelo. You da Man!

 

[Douglas Edward]

Thanks kvsrinivasamurthy! I just tried yours. It Works perfectly too!

I guess I can use either one, since they both work. Time to flip a coin. ha

Thanks guys!