Microsoft KB Archive/191021

= XL98: MOD Function and Mod Operator Return Different Values =

Article ID: 191021

Article Last Modified on 9/11/2002

-

APPLIES TO


 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q191021





SYMPTOMS
In Microsoft Excel 98 Macintosh Edition, 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) where n is the number and d is the divisor. If the divisor is a positive number, the results returned by the MOD worksheet function and the Visual Basic for Applications Mod operator are the same. For example, =MOD(17,3) on a worksheet and 17 Mod 3 on a module sheet will return the same value of 2.

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) where N is the number and D is the divisor.

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

Additional query words: XL98

Keywords: kbprb KB191021

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.