MOD Function and Mod Operator Return Different Values (141178)
The information in this article applies to:
- Microsoft Excel 97 for Windows
- Microsoft Excel for Windows 95
- Microsoft Excel for Windows 5.0
This article was previously published under Q141178 SYMPTOMS
In Microsoft Excel, 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 problem occurs if you use the MOD function with either a negative
number or a negative divisor, but not 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 formula
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, =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 number or a negative
divisor.
WORKAROUND
To return the same answer that the Mod operator returns with a negative
number or a negative divisor, enter the following formula into a worksheet
instead of using the built-in Microsoft Excel MOD function
where N is the number and D is the divisor.
NOTE: This formula only gives the same answer as the Mod operator when
both the number and the divisor are integers.
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:
119083
XL: MOD() Function Returns #NUM! Error Value
124107
XL: Can't Specify Number of Digits with Fix() or Int()
REFERENCESExcel 97
For more information about the MOD worksheet function, click Contents And
Index on the Help menu, click the Index tab in Help, type the following
text
and then double-click the selected text to go to the "MOD worksheet
function" topic. If you are unable to find the information you need, ask
the Office Assistant.
For more information about the Mod Visual Basic for Applications operator,
from the Visual Basic Editor, click the Office Assistant, type Mod.
click Search, and then click to view "Mod operator."
NOTE: If the Assistant is hidden, click the Office Assistant button on the
Standard toolbar. If the Assistant is not able to answer your query,
please see the following article in the Microsoft Knowledge Base:
176476
OFF: Office Assistant Not Answering Visual Basic Questions
Excel 7.0
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
and then double-click the selected text to go to the desired topic.
| Modification Type: | Minor | Last Reviewed: | 8/17/2005 |
|---|
| Keywords: | kbprb KB141178 |
|---|
|