Payout ties

[FivestarMac]

I have 20 participants in a pool which has a total payout of $1000 paying six places as follows: 32%, 25%, 18%, 12%, 8%, 5%. Ties split the sum of the two ranks, i.e. two first place ties split 32%+25% and the next player gets third at 18%. How can I write an equation to handle all possible payout combinations?
Thanks, FivestarMac

[martindwilson]

here is one way

[FivestarMac]

Martin,
I'm sorry for my earlier comment which I made before completely awake! I was judging on the wrong column. Let me take some time to study it and understand what you have provided.
FivestarMac

[FivestarMac]

I see now that I should have clarified that a low score is best.

[FivestarMac]

I see now that I should have clarified that a low score is best.

[martindwilson]

just change the rank to ascending

[Tony Valko]

Here's one way...

Data Range

	A	B	C	D	E	F	G
1		Score	Rank	Prize	-----	Place	Prize
2	Player1	66	1	285		1	320
3	Player2	66	1	285		2	250
4	Player3	68	3	180		3	180
5	Player4	69	4	120		4	120
6	Player5	70	5	26		5	80
7	Player6	70	5	26		6	50
8	Player7	70	5	26			0
9	Player8	70	5	26			0
10	Player9	70	5	26			0
11	Player10	74	10				0
12	Player11	74	10				0
13	Player12	74	10				0
14	Player13	75	13				0
15	Player14	75	13				0
16	Player15	76	15				0
17	Player16	76	15				0
18	Player17	77	17				0
19	Player18	77	17				0
20	Player19	77	17				0
21	Player20	77	17				0
22			Total	1000

Enter this formula in C2:

=IF(N(B2),RANK(B2,B$2:B$21,1),"")

Enter this formula in D2:

=IF(C2<=6,AVERAGE(OFFSET(G$2,MATCH(C2,F$2:F$7)-1,,COUNTIF(C$2:C$21,C2))),"")

Select C2:D2 and copy down to C21:D21

Enter this formula in D22:

=SUM(D2:D21)

Depending on the number of ties the prize may end in a long decimal value. You can format the prize values as needed to get the displayed valued that you desire.