Cesarettin noted that the MOD worksheet function cannot produce a result when the number is being evaluated is 268,435,456 or larger and the divisor is 2. If the number is less than this, there is no problem. For example, if the function is MOD(268435455, 2) there is no problem. He wonders if there is a way to use the MOD function with larger numbers and a divisor of 2?

The problem is actually bigger than what Cesarettin proposes. Microsoft knows about this problem; it seems to stem from issues with the internal formulas used by MOD. You can find more information about the error here:

http://support.microsoft.com/kb/119083

Basically, the MOD function returns an error if the divisor (the second argument in the MOD function), multiplied by 134,217,728, is less than or equal to the number being evaluated (the first argument in the MOD function).

Thus, the problem occurs when the number being evaluated is 268,435,456 and the divisor is 2, the number being evaluated is 402,653,184 and the divisor is 3, the number being evaluated is 536,870,912 and the divisor is 4, etc.

The solution suggested by Microsoft is to simply not use the MOD function and instead rely upon the following formula:

=number-(INT(number/divisor)*divisor)

This is not the only solution, however. There are other formulaic approaches you can use, as well. For instance:

=MOD(MOD(number,134217728*divisor),divisor)

This will solve for larger numbers much larger than the limit for MOD, but theoretically will hit the same problem when the number being evaluated reaches 134,217,728*134,217,728*divisor. For most uses, this is limit is large enough that it will never be reached.

If you only need to find the modulus of a number divided by 2, then you can insert a check into your formula in the following manner:

=MOD(IF(A1>=268435456,A1-268435456,A1),2)

This checks if the number being evaluated (in this case, in cell A1) is larger than the limit, and if it is it subtracts the limit from the number before calculating the modulus. You could also effectively remove the MOD limit by using this formula:

=MOD(MOD(number,2^16),2))

This takes the large number modulo 2 to the 16th power, then takes the resulting value modulo 2. If the numbers are viewed as binary, it's easy to see what is happening. MOD(largenum,2^16) just drops all bits to the left of the 16th binary digit. For modulo 2, only the right-most digit is required to determine the result anyway, so the dropped bits never affect the result, regardless of value.

Of course, you could simply create your own MOD function in VBA and use it in your formulas instead of the built-in MOD function.

Function DblMod(Dividend, Divisor) ' Declare two double precision variables Dim D1 As Double Dim D2 As Double ' Copy function arguments to local variables D1 = Dividend D2 = Divisor DblMod = D1 Mod D2 End Function

The function simply lets you pass two arguments to the VBA function. It then relies upon the VBA Mod function, which doesn't have the same limitation as the MOD worksheet function.

*ExcelTips* is your source for cost-effective Microsoft Excel training.
This tip (3302) applies to Microsoft Excel 97, 2000, 2002, and 2003.

**Program Successfully in Excel!** John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out *Excel 2013 Power Programming with VBA* today!

The INT function allows you to convert a value to an integer. The effect the function has depends on the characteristics of ...

Discover MoreWhen working with finances, you often need to know the rate of return on a given investment. The most common type of ...

Discover MoreFinding a square root is easy because Excel provides a worksheet function for that purpose. Finding a different root may not ...

Discover More**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

2017-08-02 16:27:33

2003orForgetIt

https://social.technet.microsoft.com/Forums/lync/en-US/9e6f3011-3105-4c03-a9c8-7a5360ffff5e/excel-2010-mod-bug?forum=excel

and

https://www.excelforum.com/excel-general/1090156-xl2010-formula-copying-bug.html#post4113438

https://www.excelforum.com/excel-general/1090156-xl2010-formula-copying-bug.html#post4113489

illustrate how Excel made damaging changes to the calculation engine in version 2010, though I wonder if more than 20 people on the world are conscious of it, and of course no one at Microsoft. At any rate, MOD began reporting frighteningly incorrect results beginning with version 2010. (BTW I understand floating point and Excel quite thoroughly. The point is that 2010 removed cosmetic/guard digit/FP cleanup functionality so that $12.00 to the nearest dime now reports $11.90, and it DID NOT FORMERLY DO THAT.)

Allen, what a great page you have here. Highly instructive. Concise yet specific. Useful link provided (but yikes, now invalid!). Great job. You have a presentation easily graspable by a novice, yet with details and workarounds (some are brilliant) that even an advanced user well might not know. Really, great job managing both ends, clearly, while still being concise.

By the way, 134217728 is 2 to the 27th power.

2016-06-09 09:29:08

Ferd

2015-11-23 02:02:33

Leigh

Kiran

Mod[23100001180000012345142800, 97]=18

2015-04-23 09:44:00

John

=mod((20*12.4),12.4)

evaluates "12.4"

Excel 2013 on Windows 7 32-bit

2014-12-22 03:32:40

Andrew

2014-02-11 10:18:05

number: 23100001180000012345142800

need mod 97 for the above number..

Please share why i am not able to calculate the mod 97 for this number

Please help me to get the same as i need this as urgent for my requirement.Thanks alot

2014-02-11 10:16:41

KIRAN

number: 23100001180000012345142800

need mod 97 for the above number..

Please share why i am not able to calculate the mod 97 for this number

Please help me to get the same as i need this as urgent for my requirement.Thanks alot

Got a version of Excel that uses the
menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)?
**This site is for you!** If you
use a later version of Excel, visit
our *ExcelTips* site focusing on the ribbon interface.

**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

Copyright © 2017 Sharon Parq Associates, Inc.

## Comments