Using Vlookup And Nested If In Excel For Golf Scoring

I recently received a very interesting email from a retired member of the military asking some help with a golf scoring issue. Needless to say that on so many levels I was more than happy to help out. While this blog is about “Experiments in Finance, it’s fair to say that many finance lovers like to play golf as well. It’s not the easiest problem to explain but hopefully with his comment and a preview of his spreadsheet you will get the idea. Here we go:

“I need help with writing an “IF” formula in excell. Background: I run a local golf league with 40 to 70 golfers playing each week. Instead of using strokes and keeping up with the handicaps, I use a point system. Each week I have to manually calculate each man’s score, plus or minus, from his required points.

Example: For myself I currently am required to make 45 points. If I make within plus or minus 2 of the 45 points there is no change to my next weeks requirement. However, if I make minus 3 or more points, my score will drop 1 point(to 44). If on the other hand, I score 3 or 4 points above 45, my new point requirement increases by 1 point, if I score 5 or 6 points above, my requirement increases by 2 points, if I score 7 or more points, my requirement increases to 3 points. I currently use an excel spreadsheet, listing the players . I enter their that days point total and then manually do the math and enter their new point requirement. I can continue to do the math…but in this day and age I would like to work “smarter not harder””

Here is an example of his spreadsheet:

First off, the points that he discussed are not exactly the same as the ones in the spreadsheet but that is not a problem, we are looking for a flexible solution anyway.

I used the following method:

-There are 4 possible answers:
-“a”
-a given range (in this case, between -1 and 5)
-lower than that range
-higher than that range

Basically, I will first use “if conditions” to check if any of the first 3 occur, and then refer to a range of results if it’s not the case.
The following formula looks more complicated than it really is:

=IF(C3=””,””,IF(C3=$V$4,B3+$W$4,IF(C3-B3>$V$5,B3+$W$5,IF(C3-B3<$V$6,B3+$W$6,B3+VLOOKUP(C3-B3,$V:$W,2,FALSE))))) basically, it checks if the previous score made is filled, if it the result is either "a", higher than the range, lower than the range and applies the adjustment depending on those. If none of those conditions are met, it simply checks the range. You can modify the range or values at any time by changing columns "U" and "V" You can also simply copy the blue cells to a new column "req". try copying from P3:P22 to R3:R22. Here is what the new spreadsheet looks like:

I also invite you to download the spreadsheet to see for yourself! As is always the case, please feel free to send me your own excel related issues, I’d love to help

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss

***************************************************