Draft

[joerenaud]

I haven't found this answer here so I hope that it hasn't already been asked. I'm in a golf league and am tracking season long points for all players. We play once per week and each week need to calculate rank and distribute points to each golfer based on the place finished. Rank.eq works pretty well with this and when it encounters ties, gives all those tied the same place or rank. I can then use vlookup to get the corresponding points for the place finished.

However, our league has a condition that if ties occur, rather than giving the same amount of points to all of those that tied for that same place, we need to add the point awards from the all of the golfers that tied and divide by how many of those golfers tied.

So for example, if three golfers tie for second place, and according to points distribution second place is awarded 300 points, third place is awarded 250 and fourth place is awarded 200, each of the three golfer would earn (300+250+200) / 3 or 250 points. Can anyone suggest a way to do this? Here's an example of what I've done so far.

golf_points_award_example.xlsx
	A	B	C	D	E	F
1	Name	Score	Event Place	Point Place	Point Award	Points Earned
2	John	76	5	1	500	150
3	Joe	75	2	2	300	300
4	Frank	80	7	3	250	100
5	Bill	81	7	4	200	100
6	Scott	75	2	5	150	300
7	Tom	73	1	6	125	500
8	Fred	79	4	7	100	200
9	Steve	77	2	8	75	300
10	Mike	75	1	9	50	500
11	Keith	78	1	10	25	500
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=RANK.EQ(B2,$B2:$B$11,1)
F2:F11	F2	=VLOOKUP(C2,$D$2:$E$11,2,FALSE)

Thanks for the help.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=SUMIFS($E$2:$E$11,$D$2:$D$11,">="&C2,$D$2:$D$11,"<"&C2+COUNTIFS($C$2:$C$11,C2))/COUNTIFS($C$2:$C$11,C2)