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

Obviously, as most of you can imagine, I do get a lot of different excel related questions and I do try to answer to as many of them as I can. Here is one that was posted in a comment at:

http://experiglot.wpengine.com/2011/07/13/integrating-multiple-if-conditions-in-excel/#comments

Please help. I have a very similar problem, but am not able to resolve it with COUNTIF, FREQUENCY, etc.

=IF($B$4:$B$23<H$1,IF($C$4:$C$23<$G10,FREQUENCY($B$4:$B$23,H$1),0),0)

I rarely get this exact question but I do get several requests that can be resolved in the same way. Here is how I would resolve this. First, let’s take a look at a sample of data:

I understand that it would be very easy to have the entire condition verified through one simple formula. I don’t think it’s possible in this specific case. Perhaps it is but if there is no problem with adding a column (even if it means hiding it, putting it in another sheet, etc), then it will be much easier that way. Here is how it would look like:

Then, I will simply add “if” conditions to verify the arguments and count the number. I will add conditions one at a time so to start off:

=IF(A7<$C$5,1,0) Basically, if it fulfills the first argument I get “1”. If not, 0. Then, I simply want to make sure that if it fills criteria #1, it will also do the same for #2, so: =IF(A7<$C$5,IF(B7<$C$6,1,0),0) You see the new look here:

Then, I will simply sum the number of events here:

Easy enough right?

You can download the spreadsheet here