You are currently offline, waiting for your internet to reconnect

MOD Function and Mod Operator Return Different Values

This article was previously published under Q141178
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
SYMPTOMS
In Microsoft Excel, the result returned by the worksheet MOD function maybe different from the result returned by the Microsoft Visual Basic forApplications Mod operator.
CAUSE
This problem occurs if you use the MOD function with either a negativenumber or a negative divisor, but not both negative. In general, the MODfunction returns the remainder after a number is divided by a divisor. Thebuilt-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 positivenumber, the MOD worksheet function and the Visual Basic for ApplicationsMod operator return the same results. For example, =MOD(17,3) on aworksheet and 17 Mod 3 on a module sheet will return the same value of2.

The difference between the MOD worksheet function and the Mod operatoroccurs 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 -3is equal to -5.6666667 and the closest integer that is less than or equalto -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 negativedivisor.
WORKAROUND
To return the same answer that the Mod operator returns with a negativenumber or a negative divisor, enter the following formula into a worksheetinstead 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 only gives the same answer as the Mod operator whenboth the number and the divisor are integers.

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

For additional information on both the INT and MOD functions, pleasesee 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()
REFERENCES

Excel 97

For more information about the MOD worksheet function, click Contents AndIndex on the Help menu, click the Index tab in Help, type the followingtext
MOD
and then double-click the selected text to go to the "MOD worksheetfunction" topic. If you are unable to find the information you need, askthe 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 theStandard toolbar. If the Assistant is not able to answer your query,please see the following article in the Microsoft Knowledge Base:
176476OFF: Office Assistant Not Answering Visual Basic Questions

Excel 7.0

For more information about the MOD function or the Mod operator, click theAnswer Wizard tab in Microsoft Excel 7.0 Help, type the following text
MOD
and then double-click the selected text to go to the desired topic.
5.00a 5.00c 7.00a 97 XL97 XL7 XL5 probres ATP tool pack pak XL
Properties

Article ID: 141178 - Last Review: 08/17/2005 18:16:11 - Revision: 1.3

  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 5.0 Standard Edition
  • kbprb KB141178
Feedback