How to calculate prize money when there are ties?

touque

Board Regular
Joined
Jan 22, 2009
Messages
107
I am making a golf tournament pool and I am using the prize money for our points. I have listed the prize money for each position but there are always ties.
there are 53 golfers from row 2 to 54.
i have the positions (1 to 53) listed in column K (K2:K54)
Position amounts (1st to 53rd) listed in column L (L2:L54)
score for the player in column M
calculated rank in column N (which is correct)
and the supposed money for the golfer in column O (O2:O54)

I had a sheet from a couple of years ago and the function worked great but this year it is not. Every golfer has a lower amount.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Here is the function I have right now. I dont get errors but all amounts are lower than what they should.

=IFERROR(AVERAGE(OFFSET($L$2,MATCH(N4,$K$2:$K$54,0),0,COUNTIF($N$2:$N$54,N4),1)),0)
 
Upvote 0
Timely question. The player order is current for the Masters; the scores are made up.

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]
A​
[/td][td="bgcolor:#C0C0C0"]
B​
[/td][td="bgcolor:#C0C0C0"]
C​
[/td][td="bgcolor:#C0C0C0"]
D​
[/td][td="bgcolor:#C0C0C0"]
E​
[/td][td="bgcolor:#C0C0C0"]
F​
[/td][td="bgcolor:#C0C0C0"]
G​
[/td][/tr][tr][td="bgcolor:#C0C0C0"]
1​
[/td][td="bgcolor:#F3F3F3"]
Name
[/td][td="bgcolor:#F3F3F3"]
Score
[/td][td="bgcolor:#F3F3F3"]
Rank
[/td][td="bgcolor:#F3F3F3"]
Prize
[/td][td][/td][td="bgcolor:#F3F3F3"]
Place
[/td][td="bgcolor:#F3F3F3"]
Prize
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
2​
[/td][td]Patrick Reed[/td][td]
197​
[/td][td]
1​
[/td][td="bgcolor:#CCFFCC"]
$1,980,000.00​
[/td][td][/td][td]
1​
[/td][td]
$1,980,000​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
3​
[/td][td]Rory McIlroy[/td][td]
201​
[/td][td]
2​
[/td][td="bgcolor:#CCFFCC"]
$1,118,800.00​
[/td][td][/td][td]
2​
[/td][td]
$1,118,800​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
4​
[/td][td]Jon Rahm[/td][td]
202​
[/td][td]
3​
[/td][td="bgcolor:#CCFFCC"]
$748,000.00​
[/td][td][/td][td]
3​
[/td][td]
$748,000​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
5​
[/td][td]Rickie Fowler[/td][td]
203​
[/td][td]
4​
[/td][td="bgcolor:#CCFFCC"]
$528,000.00​
[/td][td][/td][td]
4​
[/td][td]
$528,000​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
6​
[/td][td]Tommy Fleetwood[/td][td]
204​
[/td][td]
5​
[/td][td="bgcolor:#CCFFCC"]
$418,000.00​
[/td][td][/td][td]
5​
[/td][td]
$440,000​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
7​
[/td][td]Bubba Watson[/td][td]
204​
[/td][td]
5​
[/td][td="bgcolor:#CCFFCC"]
$418,000.00​
[/td][td][/td][td]
6​
[/td][td]
$396,000​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
8​
[/td][td]Henrik Stenson[/td][td]
205​
[/td][td]
7​
[/td][td="bgcolor:#CCFFCC"]
$368,500.00​
[/td][td][/td][td]
7​
[/td][td]
$368,500​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
9​
[/td][td]Marc Leishman[/td][td]
206​
[/td][td]
8​
[/td][td="bgcolor:#CCFFCC"]
$330,000.00​
[/td][td][/td][td]
8​
[/td][td]
$341,000​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
10​
[/td][td]Cameron Smith[/td][td]
206​
[/td][td]
8​
[/td][td="bgcolor:#CCFFCC"]
$330,000.00​
[/td][td][/td][td]
9​
[/td][td]
$319,000​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
11​
[/td][td]Dustin Johnson[/td][td]
207​
[/td][td]
10​
[/td][td="bgcolor:#CCFFCC"]
$286,000.00​
[/td][td][/td][td]
10​
[/td][td]
$297,000​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
12​
[/td][td]Jordan Spieth[/td][td]
207​
[/td][td]
10​
[/td][td="bgcolor:#CCFFCC"]
$286,000.00​
[/td][td][/td][td]
11​
[/td][td]
$275,000​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
13​
[/td][td]Justin Thomas[/td][td]
208​
[/td][td]
12​
[/td][td="bgcolor:#CCFFCC"]
$253,000.00​
[/td][td][/td][td]
12​
[/td][td]
$253,000​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
14​
[/td][td]Justin Rose[/td][td]
211​
[/td][td]
13​
[/td][td="bgcolor:#CCFFCC"]
$212,666.67​
[/td][td][/td][td]
13​
[/td][td]
$231,000​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
15​
[/td][td]Louis Oosthuizen[/td][td]
211​
[/td][td]
13​
[/td][td="bgcolor:#CCFFCC"]
$212,666.67​
[/td][td][/td][td]
14​
[/td][td]
$209,000​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
16​
[/td][td]Jason Day[/td][td]
211​
[/td][td]
13​
[/td][td="bgcolor:#CCFFCC"]
$212,666.67​
[/td][td][/td][td]
15​
[/td][td]
$198,000​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
17​
[/td][td]Matt Kuchar[/td][td]
212​
[/td][td]
16​
[/td][td="bgcolor:#CCFFCC"]
$187,000.00​
[/td][td][/td][td]
16​
[/td][td]
$187,000​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
18​
[/td][td]Jimmy Walker[/td][td]
213​
[/td][td]
17​
[/td][td="bgcolor:#CCFFCC"]
$170,500.00​
[/td][td][/td][td]
17​
[/td][td]
$176,000​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
19​
[/td][td]Bernd Wiesberger[/td][td]
213​
[/td][td]
17​
[/td][td="bgcolor:#CCFFCC"]
$170,500.00​
[/td][td][/td][td]
18​
[/td][td]
$165,000​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
20​
[/td][td]Charley Hoffman[/td][td]
214​
[/td][td]
19​
[/td][td="bgcolor:#CCFFCC"]
$154,000.00​
[/td][td][/td][td]
19​
[/td][td]
$154,000​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
21​
[/td][td]Tony Finau[/td][td]
215​
[/td][td]
20​
[/td][td="bgcolor:#CCFFCC"]
$143,000.00​
[/td][td][/td][td]
20​
[/td][td]
$143,000​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
22​
[/td][td]Matthew Fitzpatrick[/td][td]
216​
[/td][td]
21​
[/td][td="bgcolor:#CCFFCC"]
$127,500.00​
[/td][td][/td][td]
21​
[/td][td]
$132,000​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
23​
[/td][td]Adam Hadwin[/td][td]
216​
[/td][td]
21​
[/td][td="bgcolor:#CCFFCC"]
$127,500.00​
[/td][td][/td][td]
22​
[/td][td]
$123,000​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
24​
[/td][td]Russell Henley[/td][td]
218​
[/td][td]
23​
[/td][td="bgcolor:#CCFFCC"]
$105,600.00​
[/td][td][/td][td]
23​
[/td][td]
$114,400​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
25​
[/td][td]Si Woo Kim[/td][td]
218​
[/td][td]
23​
[/td][td="bgcolor:#CCFFCC"]
$105,600.00​
[/td][td][/td][td]
24​
[/td][td]
$105,600​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
26​
[/td][td]Kevin Kisner[/td][td]
218​
[/td][td]
23​
[/td][td="bgcolor:#CCFFCC"]
$105,600.00​
[/td][td][/td][td]
25​
[/td][td]
$96,800​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
27​
[/td][td]Satoshi Kodaira[/td][td]
219​
[/td][td]
26​
[/td][td="bgcolor:#CCFFCC"]
$84,700.00​
[/td][td][/td][td]
26​
[/td][td]
$88,000​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
28​
[/td][td]Hideki Matsuyama[/td][td]
219​
[/td][td]
26​
[/td][td="bgcolor:#CCFFCC"]
$84,700.00​
[/td][td][/td][td]
27​
[/td][td]
$84,700​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
29​
[/td][td]Francesco Molinari[/td][td]
219​
[/td][td]
26​
[/td][td="bgcolor:#CCFFCC"]
$84,700.00​
[/td][td][/td][td]
28​
[/td][td]
$81,400​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
30​
[/td][td]Haotong Li[/td][td]
220​
[/td][td]
29​
[/td][td="bgcolor:#CCFFCC"]
$74,800.00​
[/td][td][/td][td]
29​
[/td][td]
$78,100​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
31​
[/td][td]Daniel Berger[/td][td]
220​
[/td][td]
29​
[/td][td="bgcolor:#CCFFCC"]
$74,800.00​
[/td][td][/td][td]
30​
[/td][td]
$74,800​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
32​
[/td][td]Paul Casey[/td][td]
220​
[/td][td]
29​
[/td][td="bgcolor:#CCFFCC"]
$74,800.00​
[/td][td][/td][td]
31​
[/td][td]
$71,500​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
33​
[/td][td]Zach Johnson[/td][td]
221​
[/td][td]
32​
[/td][td="bgcolor:#CCFFCC"]
$66,550.00​
[/td][td][/td][td]
32​
[/td][td]
$68,200​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
34​
[/td][td]Ryan Moore[/td][td]
221​
[/td][td]
32​
[/td][td="bgcolor:#CCFFCC"]
$66,550.00​
[/td][td][/td][td]
33​
[/td][td]
$64,900​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
35​
[/td][td]Adam Scott[/td][td]
222​
[/td][td]
34​
[/td][td="bgcolor:#CCFFCC"]
$62,150.00​
[/td][td][/td][td]
34​
[/td][td]
$62,150​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
36​
[/td][td]Jhonattan Vegas[/td][td]
223​
[/td][td]
35​
[/td][td="bgcolor:#CCFFCC"]
$55,412.50​
[/td][td][/td][td]
35​
[/td][td]
$59,400​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
37​
[/td][td]Rafa Cabrera-Bello[/td][td]
223​
[/td][td]
35​
[/td][td="bgcolor:#CCFFCC"]
$55,412.50​
[/td][td][/td][td]
36​
[/td][td]
$56,650​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
38​
[/td][td]Fred Couples[/td][td]
223​
[/td][td]
35​
[/td][td="bgcolor:#CCFFCC"]
$55,412.50​
[/td][td][/td][td]
37​
[/td][td]
$53,900​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
39​
[/td][td]Bernhard Langer[/td][td]
223​
[/td][td]
35​
[/td][td="bgcolor:#CCFFCC"]
$55,412.50​
[/td][td][/td][td]
38​
[/td][td]
$51,700​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
40​
[/td][td]Webb Simpson[/td][td]
225​
[/td][td]
39​
[/td][td="bgcolor:#CCFFCC"]
$48,400.00​
[/td][td][/td][td]
39​
[/td][td]
$49,500​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
41​
[/td][td]Bryson DeChambeau[/td][td]
225​
[/td][td]
39​
[/td][td="bgcolor:#CCFFCC"]
$48,400.00​
[/td][td][/td][td]
40​
[/td][td]
$47,300​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
42​
[/td][td]Branden Grace[/td][td]
226​
[/td][td]
41​
[/td][td="bgcolor:#CCFFCC"]
$42,900.00​
[/td][td][/td][td]
41​
[/td][td]
$45,100​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
43​
[/td][td]Tiger Woods[/td][td]
226​
[/td][td]
41​
[/td][td="bgcolor:#CCFFCC"]
$42,900.00​
[/td][td][/td][td]
42​
[/td][td]
$42,900​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
44​
[/td][td]Kiradech Aphibarnrat[/td][td]
226​
[/td][td]
41​
[/td][td="bgcolor:#CCFFCC"]
$42,900.00​
[/td][td][/td][td]
43​
[/td][td]
$40,700​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
45​
[/td][td]Martin Kaymer[/td][td]
227​
[/td][td]
44​
[/td][td="bgcolor:#CCFFCC"]
$37,400.00​
[/td][td][/td][td]
44​
[/td][td]
$38,500​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
46​
[/td][td]Xander Schauffele[/td][td]
227​
[/td][td]
44​
[/td][td="bgcolor:#CCFFCC"]
$37,400.00​
[/td][td][/td][td]
45​
[/td][td]
$36,300​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
47​
[/td][td]Kyle Stanley[/td][td]
228​
[/td][td]
46​
[/td][td="bgcolor:#CCFFCC"]
$34,100.00​
[/td][td][/td][td]
46​
[/td][td]
$34,100​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
48​
[/td][td]Doug Ghim[/td][td]
230​
[/td][td]
47​
[/td][td="bgcolor:#CCFFCC"]
$31,900.00​
[/td][td][/td][td]
47​
[/td][td]
$31,900​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
49​
[/td][td]Tyrrell Hatton[/td][td]
231​
[/td][td]
48​
[/td][td="bgcolor:#CCFFCC"]
$21,615.00​
[/td][td][/td][td]
48​
[/td][td]
$30,140​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
50​
[/td][td]Chez Reavie[/td][td]
231​
[/td][td]
48​
[/td][td="bgcolor:#CCFFCC"]
$21,615.00​
[/td][td][/td][td]
49​
[/td][td]
$28,600​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
51​
[/td][td]Brian Harman[/td][td]
231​
[/td][td]
48​
[/td][td="bgcolor:#CCFFCC"]
$21,615.00​
[/td][td][/td][td]
50​
[/td][td]
$27,720​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
52​
[/td][td]Phil Mickelson[/td][td]
231​
[/td][td]
48​
[/td][td="bgcolor:#CCFFCC"]
$21,615.00​
[/td][td][/td][td][/td][td="bgcolor:#E5E5E5"]
$10,447,260
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
53​
[/td][td]Ian Poulter[/td][td]
232​
[/td][td]
52​
[/td][td="bgcolor:#CCFFCC"]
$0.00​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
54​
[/td][td][/td][td][/td][td][/td][td="bgcolor:#E5E5E5"]
$10,447,260.00
[/td][td][/td][td][/td][td][/td][/tr]
[/table]


The formula in D2 is

=IFERROR(SUM(INDEX($G$2:$G$51, C2):INDEX($G$2:$G$51, MIN(C2 + COUNTIF($C$2:$C$53, C2) - 1, ROWS($G$2:$G$51)))) / COUNTIF($C$2:$C$53, C2), 0)
 
Upvote 0
WORKED PERFECTLY! THANK YOU!
After looking at my original it looks like the top prize of $1.98M was not factored in! I have no idea why!
Thanks a million shg!
 
Upvote 0
I have a similar question as I am trying to calculate prize money for when there are ties but with a bit of variation. I have my prizes organized in rows, but also in columns depending on the number of players. As you can see I am only playing out the top 4 max, regardless of the number of players.

BCDEFGH
4​
PlyrsPurse1st2nd3rd4th5th
5​
1​
5​
5​
6​
2​
10​
10​
7​
3​
15​
15​
8​
4​
20​
15​
5​
9​
5​
25​
15​
10​
10​
6​
30​
20​
10​
11​
7​
35​
20​
10​
5​
12​
8​
40​
20​
10​
10​
13​
9​
45​
20​
10​
10​
5​
14​
10​
50​
20​
15​
10​
5​
15​
11​
55​
25​
15​
10​
5​
16​
12​
60​
25​
20​
10​
5​
17​
13​
65​
25​
20​
15​
5​
18​
14​
70​
30​
20​
15​
5​
19​
15​
75​
30​
20​
15​
10​
20​
16​
80​
30​
25​
15​
10​
21​
17​
85​
35​
25​
15​
10​
22​
18​
90​
35​
25​
20​
10​
23​
19​
95​
40​
25​
20​
10​
24​
20​
100​
40​
30​
20​
10​
25​
21​
105​
45​
30​
20​
10​
26​
22​
110​
45​
35​
20​
10​
27​
23​
115​
45​
35​
25​
10​
28​
24​
120​
50​
35​
25​
10​
29​
25​
125​
50​
35​
25​
15​
30​
26​
130​
50​
40​
25​
15​
31​
27​
135​
55​
40​
25​
15​
32​
28​
140​
55​
40​
30​
15​
33​
29​
145​
60​
40​
30​
15​
34​
30​
150​
60​
45​
30​
15​

Not every player will be present every week. What formula can I use for column D to have to below amounts correct?

ABCD
1 Name Score Place Prize
2Bob Barker
3Brandon Redmond653 $ 5.00
4Chris Wojciechowski621 $ 22.50
5Corey Selig
6Darrin Harrison653 $ 5.00
7Derek Evans621 $ 22.50
8Jack Dickman708
9Jacob Forsten708
10Johnny Cupp708
11Michael McGinnis
12Ryan Herzog696
13Shaun Adkins708
14Shayne Shroyer653 $ 5.00
15Thomas Cupp696
16Will Goeringer7412
 
Upvote 0

Forum statistics

Threads
1,224,558
Messages
6,179,512
Members
452,920
Latest member
jaspers

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top