May 27, 2019
First of all, my Excel file is a golf tournament program. An explanation of the terms used is a necessity. In golf each golfer has a handicap (anywhere from 0 to 36), there are 18 holes - each hole has a handicap (1 - 18, depending on difficulty of the hole). Depending on the Players Handicap, a number of Strokes (0, 1 or 2) is deducted from his gross number of strokes for that hole. OK, in English, if the Player Handicap is <= Hole Handicap then Strokes = 1 or 0; if the Player Handicap is > 18 then (Player Handicap - 18) =< Hole Handicap then Strokes = 2 or 1.
In VBA I used this formula: If (Hdcp Mod 18) >= cHdcp Then dots = 1 + WorksheetFunction.Floor((Hdcp / 18), 1) Else dots = 0 + WorksheetFunction.Floor((Hdcp / 18), 1) Hdcp = Player Handicap; cHdcp = Hole Handicap; dots = Strokes
I believe this can be accomplished by adding two IF columns and a third to combine the two IF columns. I would like to know if there is a formula to do this in one column?
Any ideas or help would be appreciated.
May 27, 2019
Searching the internet the problem is solved. The floor function was not necessary for this situation, a simple integer division sufficed. This was the power query formula: =if Number.Mod([Player Hcp],18) >= [Cattail Hole Handicap] then 1 + Number.IntegerDivide([Player Hcp],18) else