XL: MOD Function and Mod Operator Return Different Values

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel for the Power Macintosh, versions 5.0, 5.0a
• Microsoft Excel 98 Macintosh Edition

SYMPTOMS

In Microsoft Excel, the result returned by the worksheet MOD function may be different from the result returned by the Visual Basic for Applications Mod operator.

CAUSE

This problem occurs if you use a negative divisor with the MOD function. In general, the MOD function returns the remainder after a number is divided by a divisor. The built-in Microsoft Excel function uses the formula

   MOD(n,d)=n-d*INT(n/d)

The difference between the MOD worksheet function and the Mod operator occurs because of the way Microsoft Excel uses the INT function. The INT function returns the first negative integer less than or equal to the number. For example, =INT(17,-3) will return -6, because 17 divided by -3 is equal to -5.6666667 and the closest integer that is less than or equal to -5.6666667 is -6.

The result of using the INT function is what makes the worksheet MOD function return a different value than the Mod operator. The Mod operator does not use the same formula containing the INT function and, therefore, it returns a different result with a negative divisor.

WORKAROUND

To return the same answer that the Mod operator returns with a negative divisor, enter the following formula into a worksheet instead of using the built-in Microsoft Excel MOD function

   =N-D*QUOTIENT(N,D)

NOTE: You must have the Analysis ToolPak installed to use the QUOTIENT function.

For additional information on both the INT and MOD functions, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q119083
   TITLE     : Excel: MOD() Function Returns #NUM! Error Value

   ARTICLE-ID: Q124107
   TITLE     : XL: Can't Specify Number of Digits with Fix() or Int()

STATUS

Microsoft is researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

REFERENCES

For more information about the MOD function or the Mod operator, click the Answer Wizard tab in Microsoft Excel 7.0 Help, type the following text

   MOD