## Microsoft Excel Mod Problem

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.

1 2 3 4 5 6 7 8 |
Function GoodMod(x, y) x = CDec(x) y = CDec(y) GoodMod = Abs(x - (y * Int(x / y))) End Function |