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

 

[fhqwgads]

like this?

Player	Score	Rank	Prize	Rank	Prize
A	70	1	95	1	100
B	70	1	95	2	90
C	71	3	70	3	80
E	71	3	70	5	60
F	72	6	30	6	50
G	72	6	30	7	40
H	72	6	30	8	30
I	72	6	30	9	20
J	72	6	30	10	10

formula in D2 the cell below prize

=SUMIF($C$2:$C$10,C2,$F$2:$F$10)/COUNTIF($C$2:$C$10,C2)

just replace $C$10 and $F$10 with the last cell in your list. hope that works

 

[Douglas Edward]

That works perfectly! Thank you fhqwgads!

Now I just wish I understood how it works. ha

Any chance you could give a explanation?

 

[Douglas Edward]

I spoke too soon.

It works perfectly if you keep the rank column sorted in ascending order.

But If the rank column is in a random order, the prize column stays in descending order.

 

[Marcelo Branco]

Maybe something like this


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Player​

[/td][td]

Score​

[/td][td]

Rank​

[/td][td]

Prize​

[/td][td][/td][td="bgcolor:#D9D9D9"]

Rank​

[/td][td="bgcolor:#D9D9D9"]

Prize​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

A​

[/td][td]

72​

[/td][td]

8​

[/td][td]

10​

[/td][td][/td][td="bgcolor:#D9D9D9"]

1​

[/td][td="bgcolor:#D9D9D9"]

100​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

B​

[/td][td]

70​

[/td][td]

1​

[/td][td]

90​

[/td][td][/td][td="bgcolor:#D9D9D9"]

2​

[/td][td="bgcolor:#D9D9D9"]

90​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

C​

[/td][td]

71​

[/td][td]

4​

[/td][td]

45​

[/td][td][/td][td="bgcolor:#D9D9D9"]

3​

[/td][td="bgcolor:#D9D9D9"]

80​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

E​

[/td][td]

71​

[/td][td]

4​

[/td][td]

45​

[/td][td][/td][td="bgcolor:#D9D9D9"]

4​

[/td][td="bgcolor:#D9D9D9"]

60​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

F​

[/td][td]

73​

[/td][td]

11​

[/td][td]

0​

[/td][td][/td][td="bgcolor:#D9D9D9"]

5​

[/td][td="bgcolor:#D9D9D9"]

50​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

G​

[/td][td]

71​

[/td][td]

4​

[/td][td]

45​

[/td][td][/td][td="bgcolor:#D9D9D9"]

6​

[/td][td="bgcolor:#D9D9D9"]

40​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

H​

[/td][td]

72​

[/td][td]

8​

[/td][td]

10​

[/td][td][/td][td="bgcolor:#D9D9D9"]

7​

[/td][td="bgcolor:#D9D9D9"]

30​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

I​

[/td][td]

72​

[/td][td]

8​

[/td][td]

10​

[/td][td][/td][td="bgcolor:#D9D9D9"]

8​

[/td][td="bgcolor:#D9D9D9"]

20​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

J​

[/td][td]

73​

[/td][td]

11​

[/td][td]

0​

[/td][td][/td][td="bgcolor:#D9D9D9"]

9​

[/td][td="bgcolor:#D9D9D9"]

10​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

K​

[/td][td]

70​

[/td][td]

1​

[/td][td]

90​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

L​

[/td][td]

71​

[/td][td]

4​

[/td][td]

45​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

M​

[/td][td]

70​

[/td][td]

1​

[/td][td]

90​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td][/td][td][/td][td]

Total​

[/td][td]

480​

[/td][td][/td][td]

Total​

[/td][td]

480​

[/td][/tr]
[/table]

Formula in C2 copied down (Rank)
=COUNTIF(B$2:B$13,"<"&B2)+1

Formula in D2 copied down
=IF(C2>9,0,SUM(OFFSET(INDEX(G$2:G$10,MATCH(C2,$F$2:$F$10,0)),,,MIN(9-MATCH(C2,F$2:F$10,0)+1,COUNTIF(C$2:C$13,C2))))/COUNTIF(C$2:C$13,C2))

Just to check...
Formula in D15
=SUM(D2:D13)

Formula in G15
=SUM(G2:G10)

Hope this helps

M.

 

[fhqwgads]

basically to split between ties we need to find the average of the prize money, which is the total prize money divided by the number of people with the same score

=SUMIF($C$2:$C$10,C2,$F$2:$F$10)/COUNTIF($C$2:$C$10,C2)

to find the total prize money of people with the same score we use SUMIF

SUMIF adds numbers together based on various criteria
SUMIF(range of cells your are checking, criteria you are checking for, range of numbers to sum)
the range we are looking at is all the possible places in $C$2 to the bottom (now that i think about it, you might be able to just use the whole of column C as a reference instead of specifying C2 to whatever if you want to simplify the code a bit). the criteria we are looking for is the place that specific player got in C2. and the range of numbers sum adds the numbers that fulfill the criteria

basically it looks at what place the player got and adds all the prize money amounts that have the same place

COUNTIF gives us the number of players with the same place

COUNTIF(range you're looking in, what you're looking for)

this gives you a count of all the occurrences of the value you're looking for, in this case the player's place, in the range

sorry it's not the best explanation. i'm not great with words. the office support site have better explanations and examples

https://support.office.com/en-us/article/sumif-function-169b8c99-c05c-4483-a712-1697a653039b
https://support.office.com/en-us/article/COUNTIF-function-E0DE10C6-F885-4E71-ABB4-1F464816DF34

 

[fhqwgads]

ah sorry my solution didnt work out. hopefully other people can help

 

[kvsrinivasamurthy]

[TABLE="class: grid, width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><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"]6[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]30[/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"]40[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]

For random ranks
Formula in C2 then dragdown

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

 

[kvsrinivasamurthy]

Refering to post#8
Rank number 4 is missing. If 4 is inserted in E column formula works ok.

 

[Marcelo Branco]

A simpler formula


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Player​

[/td][td]

Score​

[/td][td]

Rank​

[/td][td]

Prize​

[/td][td][/td][td="bgcolor:#D9D9D9"]

Rank​

[/td][td="bgcolor:#D9D9D9"]

Prize​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

A​

[/td][td]

72​

[/td][td]

8​

[/td][td]

10​

[/td][td][/td][td="bgcolor:#D9D9D9"]

1​

[/td][td="bgcolor:#D9D9D9"]

100​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

B​

[/td][td]

70​

[/td][td]

1​

[/td][td]

90​

[/td][td][/td][td="bgcolor:#D9D9D9"]

2​

[/td][td="bgcolor:#D9D9D9"]

90​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

C​

[/td][td]

71​

[/td][td]

4​

[/td][td]

45​

[/td][td][/td][td="bgcolor:#D9D9D9"]

3​

[/td][td="bgcolor:#D9D9D9"]

80​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

E​

[/td][td]

71​

[/td][td]

4​

[/td][td]

45​

[/td][td][/td][td="bgcolor:#D9D9D9"]

4​

[/td][td="bgcolor:#D9D9D9"]

60​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

F​

[/td][td]

73​

[/td][td]

11​

[/td][td]

0​

[/td][td][/td][td="bgcolor:#D9D9D9"]

5​

[/td][td="bgcolor:#D9D9D9"]

50​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

G​

[/td][td]

71​

[/td][td]

4​

[/td][td]

45​

[/td][td][/td][td="bgcolor:#D9D9D9"]

6​

[/td][td="bgcolor:#D9D9D9"]

40​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

H​

[/td][td]

72​

[/td][td]

8​

[/td][td]

10​

[/td][td][/td][td="bgcolor:#D9D9D9"]

7​

[/td][td="bgcolor:#D9D9D9"]

30​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

I​

[/td][td]

72​

[/td][td]

8​

[/td][td]

10​

[/td][td][/td][td="bgcolor:#D9D9D9"]

8​

[/td][td="bgcolor:#D9D9D9"]

20​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

J​

[/td][td]

73​

[/td][td]

11​

[/td][td]

0​

[/td][td][/td][td="bgcolor:#D9D9D9"]

9​

[/td][td="bgcolor:#D9D9D9"]

10​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

K​

[/td][td]

70​

[/td][td]

1​

[/td][td]

90​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

L​

[/td][td]

71​

[/td][td]

4​

[/td][td]

45​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

M​

[/td][td]

70​

[/td][td]

1​

[/td][td]

90​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td][/td][td][/td][td]

Total​

[/td][td]

480​

[/td][td][/td][td]

Total​

[/td][td]

480​

[/td][/tr]
[/table]

Formula in C2 copied down (Rank)
=COUNTIF(B$2:B$13,"<"&B2)+1

Formula in D2 copied down
=SUMIFS(G$2:G$10,F$2:F$10,">="&C2,$F$2:$F$10,"<="&C2+COUNTIF(C$2:C$13,C2)-1)/COUNTIF(C$2:C$13,C2)

M.