# How to calculate prize money when there are ties?

#### touque

##### Board Regular
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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### touque

##### Board Regular
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
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
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!

You're welcome.

#### rscd20

##### New Member
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.

 B C D E F G H 4​ Plyrs Purse 1st 2nd 3rd 4th 5th 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?

 A B C D 1 Name Score Place Prize 2 Bob Barker 3 Brandon Redmond 65 3 \$ 5.00 4 Chris Wojciechowski 62 1 \$ 22.50 5 Corey Selig 6 Darrin Harrison 65 3 \$ 5.00 7 Derek Evans 62 1 \$ 22.50 8 Jack Dickman 70 8 9 Jacob Forsten 70 8 10 Johnny Cupp 70 8 11 Michael McGinnis 12 Ryan Herzog 69 6 13 Shaun Adkins 70 8 14 Shayne Shroyer 65 3 \$ 5.00 15 Thomas Cupp 69 6 16 Will Goeringer 74 12

#### rscd20

##### New Member
nevermind, I figured it out

Replies
2
Views
1K
Replies
0
Views
180
Replies
1
Views
418
Replies
7
Views
496
Replies
3
Views
2K

1,172,128
Messages
5,879,213
Members
433,410
Latest member
Roushcj

### 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.

### Which adblocker are you using?

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

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