Using Vlookup And Nested If In Excel For Golf Scoring

Excel spreadsheets (.xls)

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to Lynda.com. for professional help and Excel Tutorials
***************

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


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

2 Feedbacks on "Using Vlookup And Nested If In Excel For Golf Scoring"

John

I am writing from the page of “Using V Lookup and Nested If in Excel for Golf Scoring”-this is a good example of my problem. I have to use the horizontal scroolbar to see the whole page. But even then some of the right side is blank.For instance you refer in the formula to ” IF(C3=$V$4,B3+$W$4…”etc. but the columns in the diagram above end at “I”.Thus I am unable to use it as a learning aid. What is wrong with my set-up.?



golf swing flaws

Sweet blog! I found it while surfing around on Yahoo News.

Do you have any suggestions on how to get listed in Yahoo News?
I’ve been trying for a while but I never seem to get there!
Thanks



Comments

Please Leave a Comment!





Please note: Comments may be moderated. It may take a while for them to show on the page.