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.
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
Number.IntegerDivide([Player Hcp],18)
