Draft

joerenaud

New Member
Joined
Apr 8, 2011
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEF
1NameScoreEvent PlacePoint PlacePoint AwardPoints Earned
2John7651500150
3Joe7522300300
4Frank8073250100
5Bill8174200100
6Scott7525150300
7Tom7316125500
8Fred7947100200
9Steve772875300
10Mike751950500
11Keith7811025500
Sheet1
Cell Formulas
RangeFormula
C2:C11C2=RANK.EQ(B2,$B2:$B$11,1)
F2:F11F2=VLOOKUP(C2,$D$2:$E$11,2,FALSE)


Thanks for the help.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,728
Office Version
  1. 365
Platform
  1. Windows
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
Excel Formula:
=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)
 

Forum statistics

Threads
1,147,747
Messages
5,742,964
Members
423,769
Latest member
LongToast

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