Awarding points with a rank function......

[ehatz]

I have been working with a scoring template for golf where I want to award points to the top 10 finishers in the event. I have been able to get the formulas to automatically populate the points for the top 10 and even break ties by adding up the points possible for all that have tied and divide them up. My problem is that if two players tie for 10th it gives 1 point to one of them and .5 to the other. If 3 or more players tie for 10th it defaults to zero points. I need 10th place to receive 1 if no tie (which this does) but if there is a tie, anyone that ties for 10th needs to receive .5 points (tournament minimum).

Any suggestions or solutions? I have attached a sample of what I am working with. On the left is what it is currently generating and on the right is what I want it to show.

Points Break Down.xlsx

[TMS]

Maybe:

Please Login or Register  to view this content.

Regards, TMS

[ehatz]

After trying that it just gives me the response #DIV/0!

[TMS]

Apologies, I had added a helper column with the pure rank.

See the attached workbook; I made a copy of the data so I could sort it and compare. Hope this works for you.

Regards, TMS

[daddylonglegs]

Try this formula for row 9 copied down

=IF(COUNTIF(B$9:B$23,"<"&B9)<10,MAX(11-RANK.AVG(B9,B$9:B$23,1),0.5),0)

[ehatz]

Excellent! Both seem to work very well.