How to calculate prize money when there are ties?

touque

Board Regular
Joined
Jan 22, 2009
Messages
98
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 is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

touque

Board Regular
Joined
Jan 22, 2009
Messages
98
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)
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,797
Office Version
  1. 2010
Platform
  1. Windows
Timely question. The player order is current for the Masters; the scores are made up.

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

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)
 

touque

Board Regular
Joined
Jan 22, 2009
Messages
98
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!
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,797
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

You're welcome.
 

rscd20

New Member
Joined
Jan 23, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,136,257
Messages
5,674,665
Members
419,521
Latest member
Jennifer4Dillon

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
Top