Mark Mintoff My superpower is common sense


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.


VN:F [1.9.22_1171]
Rating: 5.0/5 (2 votes cast)