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.

[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)

 

[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