Show Us Your Handicap League Spreadsheet

[3j0hn]

A spreadsheet to track handicaps for a weekly league is one of those wheels that we all keep reinventing. I'd like to get a nice collection of people's spreadsheets here so that someone trying to start a new league can see what other people have done.

I'll start with my elaborate contribution to this genre. The league in question plays the same layout every week and bases handicaps on your last five rounds using a centered average (drop the highest and lowest score and take the average of the other three).

The spreadsheet is here (it has a lot of sheets):
https://docs.google.com/spreadsheet/ccc?key=0Aiupe6tk7j8RdFJZTUtRRF83SWJuMjlHMmxmTnVlWUE&usp=sharing

Since it is hosted on Google Docs, the live version of the spreadsheet can be used from any smart phone to tabulate the results of an event and calculate handicapped standings and payouts (it uses a version of PDGA payout tables calibrated so that last cash is $3 = entry fee).

Over time I even added a summary of standings for the season. This includes a 1000-based rating using a linear fit of player's scores each week so that people get a "League Rating". It also tracks points based on how many people you beat on a given week (even though tracking points-series this way for handicap events is probably not a great idea).

Feel free to copy my spreadsheet and use it for your own league if you think it would be useful. Please share any changes or feedback, if you do.

If you have made your own spreadsheet to run a league, please share it!

 

[Jukeshoe]

This is some good stuff...thank you for sharing! :thmbup:

 

[Cgkdisc]

www.discgolfunited.com

 

[snappyfingers]

Here is what we use at OG

AM Weekly Handicap League

 

[3j0hn]

www.discgolfunited.com

Of course I built my project before I knew about the handicapping at DGU. The $0.25 per score doesn't really seem like a large overhead for running a league, but I suspect there are lot of people doing DIY who might otherwise use it. I wonder if a flat rate per week would make people more inclined to use it.

My other question is whether it works well from a smart phone. A spreadsheet has the advantage that it can been used in Google docs in offline mode.

 

[Cgkdisc]

Separate from handling league calculations and handicaps, it allows each player to track their personal handicap for all rounds entered and their handicap is universal, not tied to a particular course. It's built on the same ratings process as PDGA. The course database uses the same actual PDGA tournament data to produce the course values needed for fixed course rating calculations. In addition, it has both accelerated and decelerated decay functions built-in so more recent rounds are more heavily weighted and a built-in defense for sandbagging attempts. This is all automated and standardized so the League Director doesn't have to cook up special manual procedures in Excel.

 

[discgman]

Here is a snippet of our handicap system in Norcal. Took a few sessions to work out all the bugs.



Handicaps are based on your last 5 weeklies. The average of your scores during that time is subtracted from 54 (the par 3 total of the course) and multiplied by .9. Let's pick on Vic as an example to fully understand:

Player Week 1 Week 2 Week 3 Week 4 Week 5 Average Handicap
Vic 57 52 53 59 62 56.6 2.34


He shoots an average of 56.6 per weekly. We subtract 54 to give us his average of 2.6 over per round. That is multiplied by .9 (90%) to give us his Handicap of 2.34. This means he starts at -2 when he plays his next weekly.

If you have not participated in 5 series events but have established your handicap, the last three or four completed rounds will be used. Handicaps are rounded up i.e. if your handicap is 4.55, your rounded handicap is 5. If your handicap is -4.55 your rounded handicap is -5. Handicaps cannot exceed -6.

 

[DixieDG]

Have been developing a tournament/league database site and have many requests for a handicap system. Would love to see a broad-based agreed upon formula that could be incorporated into the site.

Purely beta site for testing at this point ........... http://www.msdiscgolf.com/league/leagues.php

 

[3j0hn]

Verbose thoughts on Handicap

Any handicapping system, as long as it is applied consistently, can work fine for a weekly league I think. Most often, the handicap systems leagues use are chosen to be easy to explain to players rather than soundly designed to achieve the goal:

"The player who plays the best relative to their average performance should win."​

and that is okay, but since I've taught math in the past, I tend to obsess about trying to explain to people what is wrong with their systems. That doesn't usually go well. But (warning) I am going to do that here.

If I were building a league management website, I would use a system like discgman's, but without the 0.9 multiplier and rounding to one decimal place instead of to whole numbers. I would allow the League Director to choose how many weeks to include in handicaps (4 to 6 is common I think) and whether to drop outlier scores (highest and/or lowest e.g.).

If you want to allow people to include a multiplier in their handicap calculation, I would make sure it is done with respect to a "scratch score" (e.g SSE) for the course rather than to par.

Here is an example of why to do this: if you are playing an easy SSE 46 Par 54 course and you have a 970 rated player, they could easily have an average of -7 (47). Applying a 0.9 multiplier, they are playing to a handicap of 6.3 strokes making it so they only have to shoot their average in order to get a -1 (ish) adjusted score. On the other hand, the 770 rated player could have an average of +7 (61) and the 0.9 multiplier then means that they get a +1 (ish) adjusted score if they shoot their average. This is definitely not fair if we assume that it is just as easy for each of these players to shoot better than their average score. A smaller multiplier (say 0.75) actually makes this less fair.

Better choice is to adjust relative to the SSE:
Generally: (Average - SSE) * 0.9 - (Par - SSE) = -Handicap
Example 970 player: ( 47 - 46 ) * 0.9 - (54 - 46) = -7.1 (he has to shoot 0.1 better than his average to get an adjusted score of 0)
Example 770 player from above: ( 61 - 46 ) * 0.9 - (54 - 46) = 5.5 (he has to shoot 1.5 better than his average to get 0)

Doing it this way, both players have to shoot better than average to get an adjusted score of 0. The 770 player has to shoot "more better" than the 970 player (which it why I don't like using multipliers at all) but it is a bit more fair.

If your league is all (say) 850 - 925 rated players, this sort of thing won't come into play a lot, but if you have a wider range of players you have to be more careful. The league for which I wrote the spreadsheet in the OP (which doesn't use a multiplier) has a range of players of about 670 to 970 ratings (that's score range of 33 strokes between best and worst on our SSE 49 course) and last summer, everyone who played more than three rounds cashed at least once, and the top "cashers" for the summer were spread across all skill levels.

tl;dr: just use a centered average for handicaps; it's easy and pretty fair

 

[discgman]

Good read

I think the reason the .9 multiplier is used is because it was hard to figure out the ssa for the course due to it being a 9 hole course and never had any official tournament play out there. It seems to be working as my "average" round is around -7, which after handicapp that is maxed out (-6) I end up with a -1 round which would barely get me in cash for a 40 percent payout.

The goal for the league, for me, is to grow the sport by getting more beginners involved at the local level, while keeping the advanced players honest by having to shoot above average rounds to even cash. And since the course was designed for the intermediate level player anyways, its sort of geared toward them.

 

[3j0hn]

I think the reason the .9 multiplier is used is because it was hard to figure out the ssa for the course due to it being a 9 hole course and never had any official tournament play out there. It seems to be working as my "average" round is around -7, which after handicapp that is maxed out (-6) I end up with a -1 round which would barely get me in cash for a 40 percent payout.

The goal for the league, for me, is to grow the sport by getting more beginners involved at the local level, while keeping the advanced players honest by having to shoot above average rounds to even cash. And since the course was designed for the intermediate level player anyways, its sort of geared toward them.

The DGCR SSE for courses is a decent proxy for a PDGA SSA (for these purposes). It doesn't matter too much if it is a couple strokes off. In your case, since you are capping handicaps at -6, you could use 47 or 48 as your SSE.

Of course, in your case, I think the cap and the rounding do all the heavy lifting, and you don't really need the 0.9 multiplier at all. I would be sure to always round down (instead of towards 0) though: less skilled player with a +7.4 average rounds down to +7, and the more skill player with a -5.4 rounds down to -6. Personally, though, I like to keep the decimal as a tie breaker.

 

[discgman]

The DGCR SSE for courses is a decent proxy for a PDGA SSA (for these purposes). It doesn't matter too much if it is a couple strokes off. In your case, since you are capping handicaps at -6, you could use 47 or 48 as your SSE.

Of course, in your case, I think the cap and the rounding do all the heavy lifting, and you don't really need the 0.9 multiplier at all. I would be sure to always round down (instead of towards 0) though: less skilled player with a +7.4 average rounds down to +7, and the more skill player with a -5.4 rounds down to -6. Personally, though, I like to keep the decimal as a tie breaker.

The one complaint like I am sure all others is that there are some players milking the handicapps. They are decent players who shoot good rounds then a few bad ones which keep their handicapps the same. I wonder if I lost the multiplier and round down like you said, it would fix this.

 

[3j0hn]

The one complaint like I am sure all others is that there are some players milking the handicapps. They are decent players who shoot good rounds then a few bad ones which keep their handicapps the same. I wonder if I lost the multiplier and round down like you said, it would fix this.

Dropping players' worst round before taking the average can help with that too.

 

[TrustYourRubber]

This is the run-down we're going to try this year... don't have any scores yet to test it out, so we might have to wait a little while to see how this all works out, or doesn't work...

So keep up if you can understand my views and ideas...

On my local course I have seen -13 all the way up to +24 in league play. On our course we have 2 tee boxes that would be considered a par 4 by DGA course par guidline standards.

Say the guy who hits -13 has a 1 to 1 ratio starting them at +13 for the round
All the way to -4 x -1 = 4 so this player has a +4 handicap
-3 x -.9 = 2.7 so this player still has a +3 handicap
shoots +3 x -.9 = -2.7 HC -3
+4 x -.7 = -2.8 HC -3
+7 x -.7 = -4.9 HC -5
+8 x -.65 = -5.2 HC -5
+9 x -.65 = -5.85 HC -6
+10 x -.6 = -6 HC -6
+13 x -.6 = -7.8 HC -8
+24 x -.6 = -14.4 HC -14

If everyone shoots their average that average +3 on up will shoot a zero
+4 player ends with 1 over
+5 avg. = 1 over
+6 = 2
+7 = 2
+8 = 3
+9 = 3
+10 = 4
+11 = 4
+12 = 5
+13 = 5
+24 = 10

 

[3j0hn]

If everyone shoots their average that average +3 on up will shoot a zero
+4 player ends with 1 over
+5 avg. = 1 over
+6 = 2
...
+13 = 5
+24 = 10

I don't know if you intend this, but I think that this system to going to really punish players with higher averages. If your averages are based on enough (4+) rounds, then the chances that a player with a +13 average shoots +8 or better are generally going to be much lower than the chances that a +3 players shoots +3 or better but yet this system gives them the same handicap adjusted score.

If you stuck with a 0.8 multiplier (and round down) for everyone with a positive average. Then that turns into:

If everyone shoots their average:
Those with average of 0 or lower will shoot a zero
+1 avg player ends up with 1 over
+6 => 2 over
+11 => 3 over
+16 => 4 over
+21 => 5 over
+27 => 6 over
​

This is a simpler system, but still tilts things against high average players (if you think they are more likely to have large swings in their scores). If you think you need to tilt things even more, just choose a lower multiplier. Even a 0.6 multiplier doesn't change things very much for players with averages below +6.

 

[zrxchris]

DGUnited has had many recent improvements in the last few months. I highly reccomend it.
Once a player has 4 rounds in the database, it is very accurate. Just like league, if you're a top local player, you have to play very well to best your projected score.

 

[davetherocketguy]

www.discgolfunited.com

That's a nice site and looks like it would work great but man am I having a lot of trouble with it. I need to be able to add players without an email address and it just isn't letting me do that...Going to have to switch to something else. Also, there's a charge for rounds? Where does it say that? I am trying to figure out what my costs are going to be for using this whether I can just do it out of pocket or I have to charge or what...

 

[davetherocketguy]

Also, it would be REALLY nice to be able to have something I could use on my iPhone so I could record scores right then and there at league. I've been working 50-60 hours a week and I don't have time for messing around with something complicated. The spreadsheet earlier in this thread looks great but holy time commitment batman...

 

[davetherocketguy]

So after A LOT of thought and reading this thread...and re-reading it here's my version:

https://docs.google.com/spreadsheets/d/1zSZdUmG-QESfA2VEX1wbClNE0wiTVthYwy-3zjTPbaI/edit?usp=sharing

So the idea is this: You win on the PERCENT difference from your average. I left the SSA and SSA plus Handicap columns on there just for fun but ultimately the way it would work is like this:

score / average - 1 = percent change

The lower the better. So for example player one averages 54.6 and scores a 53:

53 / 54.6 - 1 = -2.93%

Player two averages 70.6 and scores a 69:

69 / 70.6 = -2.27%

Obviously, both players scored 1.4 lower than their average but player one wins because with a lower average score each throw is far more significant. Player two has many more throws to work with to lower their percentage so to me, handicap is irrelevant in this case but your percent improvement versus your average is what counts.

Usually the goal for handicapping is a measure that ball golfers could use across many courses based on several factors including course slope rating, 5 rounds recorded, etc. which really doesn't apply here. In this case, I am trying to achieve a relative scoring system where players across a broad skillset can compete with each other on a single course and a single format.

-Dave

 

[3j0hn]

Kudos to davetherocketguy for actually uploading a spreadsheet.

Have you tried this system in an actual league? I am all for trying to things other than the simple "strokes off your average" handicapping but I am never sure how open players will be to it.

But...

Obviously, both players scored 1.4 lower than their average but player one wins because with a lower average score each throw is far more significant.

I am not sure that I find this obvious. One of the most consistent players in one of my leagues was a guy shooting about +14 worse than the best players. So 2 strokes better than average for him would be harder than 2 strokes below average for anyone else in that league - so he would have a hard time ever winning with percent based handicaps.

My inner math nerd thinks you should track players' Average and Standard Deviation and then award the winners by their z-score rather than just a percent (these are all easy to calculate in a spreadsheet). But your average player is not likely to be as familiar with statistics as they are with percentages.