How to calculate prize money when there are ties?

[touque]

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.

 

[touque]

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]

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]

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]

You're welcome.

 

[rscd20]

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]

nevermind, I figured it out