Quick note: For those looking for Week 14 scores, they are below this post.
As some of you noticed, there was quite a delay in getting some of the scores posted prior to Week 14 play. While the IT Nerd being on vacation and not having his normal laptop with him were factors, they weren’t the main reason. The main reason was that there was a bug in the quota calculation formula. The bug was found immediately prior to the start of play for Week 13.
How the bug was found:
An early make-up between Jon Carroll and Mike Costello on 7/20 was submitted and they requested their updated quotas. Most times, I just enter them into the tracker and let it do the work. For whatever reason, I did them by hand…and they weren’t matching to what the sheet was calculating.
I also noticed something odd in the quota calculation for Mike Costello. The quota formula was returning an “#N/A” for a round that I know Mike had played. Below is an example. At that point, Mike had played 12 rounds in 2023 and the formula found his 11th round score, 9th round score, 8th round and 7th round (5 rounds). You can see that his 10th round was unable to be found, thus producing an “#N/A” (error) and an error in the overall formula (last column is supposed to be the result).
What caused the bug:
The short answer is double matches and our approach to them this year as well as the capability to play any matches throughout the year.
Each round a player completes is timestamped by date and time (Date Played column). The “P count” column (short for “Played Count”) is there to determine the sequence in which those matches were played. In order to determine that order, the sheet using a “RANK.EQ” formula which ranks those dates from earliest to latest. Mike Costello played his first round on 4/27 at 5 pm, second round on 5/11 at 5 pm, etc., etc.
In the past, when there were double matches, one match would be timestamped at say 17:00 while the second would be timestamped at 17:01 after they were entered so that the quota would remain the same for that double match. When the quota calculation would run for the following week, it would see two scores in very rapid succession and factor them both in for quota calculation despite the fact that it was only one actual round of golf.
This year, the change was made to timestamp them at the same time to avoid that double counting. Unknown to the IT Nerd, when the RANK.EQ formula runs and sees two results at the same time, it calls them a draw. You can see an example of that in the above. Note Mike Costello’s round 8 was played 7/6 at 15:00 (early round), then his 9th round was a double match on 7/6 at 17:00 (normal time). Note that there are two “9th rounds” and no 10th round.
Bringing back to Mike Costello’s quota calculation:
The bottom row of it asks what is the highest number of rounds someone has played for the year. In Mike’s case, it’s 12. It then simply subtracts 1 (resulting in 11), subtracts 2 (resulting in 10), etc., etc. until we get all 6 rounds. Prior to this year, all rounds had to be completed in sequential order and prior to the next weeks play. Therefore, the “max round count minus 1, then minus 2, etc…” would work.
Note that because there was a tie on the timestamp for his 9th rounds, the formula doesn’t produce a 10th round…it just goes right to 11. When the formula tries to find what Mike shot for his 10th round, there is no tenth round, so it returns error. Since there is an error in one cell of the formula, the entire formula returned an error.
An informed excel user might then ask the question: If the quota formula was producing an error, how was it that it wasn’t really caught until later on? Wouldn’t people have received “errors” for handicaps earlier?
The IT Nerd’s response is simple: That formula error is common for people who do not yet have 6 rounds like someone who is new to the league. When it sees that error, it thinks the person is new and doesn’t have the necessary rounds for a quota adjustment and returns back the quota that the person started with. Hence, it wouldn’t produce an error, but it might produce an incorrect quota.
How do we know who this could have impacted?
Key word there is could have impacted. The easy way to find out who could have been impacted was to look to see who also has that error in their quota calculation. At that time, it was found that there were twelve (12) golfers who could have been impacted.
At the conclusion of play on Week 13, the IT Nerd met with the TBO Board to seek direction: Do we go back and make the quotas accurate on matches that have already been played only weeks ago or do we let the past be what it is an move forward with the corrections?
The decision of the Board was to have accurate quotas and adjust any matches to reflect what the quotas should have been. There’s more detail later on in the post, but only 3 golfers were actually impacted and it did not impact the other 9.
How were the impacts assessed?
For those who played a double match, their quotas were only impacted after they played the double match. The IT Nerd then went back in time for each of the impacted golfers and hand calculated what the quotas should have been for each match, what impact that had on the match (if at all) and make the adjustments. Below is a screenshot of the hand calculations for one of the impacted golfers, Jon Carroll. Jon was particularly impacted because his first double match was much earlier and had more of them.
Who was actually impacted?
After all of the hand calculations, only three golfers were impacted in terms of wins-losses-draws:
- Jon Carroll
- Week 11 match vs. Costello – Jon’s quota on the card was listed as an 8, but he should have been a 10. On the card, Jon beat Costello by 1 point. With Jon’s quota shifting from the incorrect 8 to the correct 10, that becomes a loss for Jon and a win for Costello.
- Week 12 match vs. Mike Mahar – Incorrect Quotas on the card had Jon at an 8 and Mike at a 7. The correct quotas were Jon as a 10 and Mike Mahar as a 5. On the incorrect quotas, Jon won by 4. With Jon’s quota going up 2 and Mike’s going down 2, that turns that match to a tie.
- Mike Mahar
- Week 12 match vs. Jon Carroll mentioned above – Was a loss based on incorrect quotas, now is a tie.
- Week 11 match vs. Brian Thayer – Mike was listed as a 7 and should have been a 5. On the card, he lost the match to Thayer by 1 point. With the adjustment from an incorrect 7 down to a correct 5, Mike now wins the match.
- Marty Donegan
- Week 7 match vs. Rob Manipole – Marty was incorrectly listed as a 9 and his quota should have been a 10. Based on the incorrect card, Marty won that match by 1 point. When the correct quota is applied, it becomes a tie between Marty and Manipole.
The other 9 golfers who had no impacts to their wins-draws-losses were Rob Manipole, Tom Donegan, Mike Costello (aside from the impacts from Jon’s match), Joe Donegan, Bill Kirkby, Dan Keeley, Jim Morgan, Mike Shanahan, John Mooney.
What fixes were put in place and how do we know it worked?
The fix came in two steps:
The first step was to avoid the ‘tie’ in the rankings. I repurposed the next column over to run a second check on the “P Count” column. When it sees the second instance of a round at the same time, it returns a blank space. You can see the formula in action below for Mike Costello’s second instance of his 9th round.
The second step was to replace the “max rounds minus 1, minus 2, etc.” formula. The IT Nerd needed a formula which would automatically pick the latest round, second latest round, etc., until it picked up the last 6 rounds. Perfect function for this is the LARGE function within excel and to have it reference the newly created “Date Counts” field which eliminates the draws. Example of the formula in action is below. You can see it jump from Round 11 to Round 9 because there is no Round 10 (at least, in the formulas).
With the formulas working, they were replicated across the entire league and have been verified to be functioning properly.
Apologies on the length, but this is the stuff the IT Nerd loves talking about!!
