# Large Numbers in the MOD Function

Written by Allen Wyatt (last updated August 22, 2020)
This tip applies to Excel 97, 2000, 2002, and 2003

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.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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

##### Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

##### MORE FROM ALLEN

Changing the Language Setting for All Document Text

Word supports a number of different languages for documents you create. If you need to routinely change the language used ...

Discover More

Changing the Starting Page Number

Word normally numbers pages in a document starting at one and extending as far as the number of pages you have. If you ...

Discover More

Using the CONCATENATE Worksheet Function

The process of combining string (text) values to make a new string is called concatenation. Excel provides the ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

##### More ExcelTips (menu)

Determining the Least Common Multiple

Need to figure out the least common multiple of a range of values? It is a snap when you use the LCM function, described ...

Discover More

Calculating Combinations

The COMBIN function is used to determine the number of combinations that can be made from a group of elements. This tip ...

Discover More

Summing Only Positive Values

If you have a series of values and you want to get a total of just the values that meet specific criteria, then you need ...

Discover More
##### Subscribe

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

View most recent newsletter.

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. Youâ€™ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is one more than 0?

2021-12-06 22:21:55

Eric

I just want to say "thank you". For the weird behavior of MOD has caused me hours of frustration. =number-(INT(number/divisor)*divisor) does the trick.

Here is a frustrating example =MOD(2357.74,0.77) returns 0.77, what!? My name is Batman if this should not have returned 0 !!!

Eric

2020-09-12 17:49:21

Joe User

Errata.... I wrote: ``1125900000000 [....] does fit into a 32-bit binary floating-point (type Single)``. No, it doesn't.

2020-09-12 15:34:15

Joe User

Some of the details in this article appears to be obsolete.

I do believe MOD(n,d) returned #NUM for n >= d*2^27 (134217728) in Excel 2003 and earlier.

But at least as of Excel 2010 (and perhaps Excel 2007), the threshold is larger.

Now, MOD(n,d) returns #NUM for n >= d*1125900000000.

YMMV for versions after Excel 2010, which I cannot test. But MOD(1125900000000,1) does return #NUM in Excel Online (onedrive.live.com) -- today.

Anyway, the following do __not__ return errors in Excel 2010 and Excel Online: MOD(134217728,1), MOD(268435456,2), MOD(402653184,3), MOD(536870912,4).

FYI, 1125900000000 is an unusual number. It is not a simple power of 2. It is 13 digits, which is less than 15. It does fit into a 32-bit binary floating-point (type Single); but so do a lot of other values, larger and smaller.

In any case, with modern computers, there is no good reason (IMHO) not to calculate MOD straight-forwardly using 64-bit binary floating-point (type Double).

2020-09-12 05:17:31

Joe User

DblMod = D1 Mod D2
results in an overflow error when D1 exceeds 2^31-1 because VBA Mod converts its operands to type Long.
A more-reliable alternative is
DblMod = D1 - D2 * Int(D1 / D2)
which is, after all, the alternative "suggested by Microsoft"

2020-09-12 05:02:15

Joe User

http://support.microsoft.com/kb/119083 is no longer available.
Is there an alternative URL that still works?

2020-08-22 10:38:39

Rick Rothstein

Here is a function that I have posted in the distant past (back in the old VB newsgroups) that can calculate the Mod using humongous integer numbers for either argument...

' The arguments to the BigMod function can be up to 29 digits as long as
' either number is less than 79,228,162,514,264,337,593,543,950,335
' (a limitation of the Decimal data type used in the calculations).
' And, of course, if you are going to feed the BigMod function super
' large numbers, they must be passed in as quoted text strings.
Function BigMod(Number As Variant, Modulus As Variant) As Variant
Number = CDec(Number)
Modulus = CDec(Modulus)
BigMod = Number - Modulus * Int(Number / Modulus)
End Function

##### This Site

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.