There exists a problem in Microsoft Excel's Mod function, whereby performing modulo on decimal numbers results in incorrect remainders.
For instance, MOD(0.17, 0.17) results in 0 (as expected), whereas MOD(0.51, 0.17) results in 0.17 (incorrect).
In fact, a modulo on the following multiples of 0.17 has resulted in many erroneous reports of remainders:
The reasoning behind this is that internally a float data type is used which results in an incorrect modulo to be calculated. In fact, if you try this out in C# using float or double, you will get the exact same results.
The solution, I have found is to use the formula for modulo:
x - (y * INT(x/y))
To this end, I have created a simple function through VBA which achieves a correct result by enforcing the usage of the decimal data type.
Function GoodMod(x, y)
x = CDec(x)
y = CDec(y)
GoodMod = Abs(x - (y * Int(x / y)))