Article ID: 214271
Article Last Modified on 11/23/2006
APPLIES TO
- Microsoft Excel 2000 Standard Edition
This article was previously published under Q214271
SYMPTOMS
In Microsoft Excel 2000, the result returned by the worksheet MOD function may be different from the result returned by the Microsoft Visual Basic for Applications Mod operator.
CAUSE
This behavior can occur if you use the MOD function with either a negative number or a negative divisor, but not with both negative. In general, the MOD function returns the remainder after a number is divided by a divisor. The built-in Microsoft Excel function uses the following 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 MOD worksheet function and the Visual Basic for Applications Mod operator return the same results. For example, if you insert =MOD(17,3) in a worksheet and 17 Mod 3 in a module sheet, each returns 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) returns -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 Mod operator does not use the same formula containing the INT function and, therefore, it returns a different result in the case of a negative number or a negative divisor.
WORKAROUND
To return the same result that the Mod operator returns with a negative number or a negative divisor, type 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: This formula returns the same result as the Mod operator only when both the number and the divisor are integers.
NOTE: You must have the Analysis ToolPak installed to use the QUOTIENT function.
REFERENCES
For additional information about both the INT and MOD functions, click the article number below to view the article in the Microsoft Knowledge Base:
119083 XL: MOD() Function Returns #NUM! Error Value
For additional information about both the INT and MOD functions, click the article number below to view the article in the Microsoft Knowledge Base:
213828 XL: Can't Specify Number of Digits with Fix() or Int()
For more information about the MOD worksheet function, click Microsoft Excel Help on the Help menu, type mod in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
For more information about the Visual Basic for Applications Mod operator, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type mod operator in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
NOTE: If the Assistant is hidden, click the Office Assistant button on the standard toolbar.
For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
231955 OFF2000: Office Assistant Not Answering Visual Basic Questions
Additional query words: ATP tool pack pak XL2000
Keywords: kbprb KB214271
