Because of my recent terrible putting, I decided to keep track of every putt to see what I can see about it. Here’s how I compiled the stats.

I wrote down how many paces each putt was and assumed one step was 2.5 feet. The Made formula in the table is

1 |
=SUMPRODUCT(([Date]=[@Date])*([Hole]=[@Hole])*([Putt]=[@Putt]+1))=0 |

For that date and hole, see if there’s a putt with a higher number. If not, I must have made that one. The Left formula is

1 |
=SUMPRODUCT(([Date]=[@Date])*([Hole]=[@Hole])*([Putt]=[@Putt]+1)*([Length])) |

Same as the last except that it sums the length of the putt that’s one higher. Over in the stats table, I use these formulas

Made:

1 |
=SUMPRODUCT((tblPutts[Length]>=I3)*(tblPutts[Length]<=J3)*(tblPutts[Made])) |

If it’s greater than or equal to the first number and less than or equal to the second number and I made it, count it.

Made %:

1 |
=IFERROR(K3/SUMPRODUCT((tblPutts[Length]>=I3)*(tblPutts[Length]<=J3)),0) |

Take the number made and divide it by the total putts in that distance range. The IFERROR is for divide by zero errors.

Avg Left:

1 |
=IFERROR(SUMPRODUCT((tblPutts[Length]>=I3)*(tblPutts[Length]<=J3)*(NOT(tblPutts[Made]))*(tblPutts[Left]))/SUMPRODUCT((tblPutts[Length]>=I3)*(tblPutts[Length]<=J3)*(NOT(tblPutts[Made]))),0) |

This sums all the “Left” numbers for putts not made and divides by the number of putts not made.

Max Left:

1 |
{=MAX((tblPutts[Length]>=I3)*(tblPutts[Length]<=J3)*(NOT(tblPutts[Made]))*(tblPutts[Left]))} |

This one’s an array formula, so enter with Ctrl+Shift+Enter, not just enter.

In retrospect, I should have noted putts that went past the hole as negative distances and putts that were short of the hole as positive distances. I could have got the same stats as above plus I would know if I had a tendency to be short or long. It feels like enough bookkeeping for the golf course already, but maybe I’ll implement that change in the future.

So, what did I learn? I learned that I like numbers and statistics, but I don’t think I learned anything useful for my golf game.

You can download PuttingDistance.zip

Do your golfing buddies give you a hard time when you whip out your laptop and start pacing back and forth?

I don’t play golf, and yet I have something like 5000 golfballs in my basement. We live near a golf course that borders a council reserve with lots of scrub that has a large fence around it. Me and my 13 year old son go out occasionally at night with a UV torch, as the balls fluoresce quite nicely and so are very easy to find . It’s like taking candy from an old person.

We clean ’em up, bag ’em up, and sell them on the New Zealand equivalent of ebay back to that old person. We’ve probably sold something like 8000 balls over the last 4 years, and we’ve posted them to practically every town in New Zealand. Kids are never short of cash, to the point that I have to nag them to go out with me and keep relisting as we sell dozens.

My buddies have long since given up on making fun of my nerdishness. I just wore them out.