Large Numbers in the MOD Function

by Allen Wyatt
(last updated February 29, 2016)


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:

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:


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


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:


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:


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.


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. ...


Getting the Proper Type of Ellipses

Type three periods in a row, and the AutoCorrect feature in Word kicks in to exchange that sequence for a special ...

Discover More

Creating a Header

When preparing your worksheet for printing, you may want to add a header that appears at the top of each page that you ...

Discover More

Combining Numbers and Text in a Cell

There are times when it can be beneficial to combine both numbers and text in the same cell. This can be easily done ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (menu)

Using the XIRR Function

One of the financial worksheet functions provided in Excel is the XIRR function. This is used to figure out an internal ...

Discover More

Rounding Numbers

The primary method of rounding values is to use the ROUND function in your formulas. Here's an introduction to this ...

Discover More

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

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}] 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 9?

2020-06-25 09:14:49

Ramesh Soni

Neither MOD nor your alternate suggestion worked for MOD ((10^n-3), 29), when "n" reached 15 and beyond. Of course, the MOD function not working brought me your page to begin with. But, even "Number - INT(number/divisor)*divisor" did not work.

2019-09-17 13:40:36


The problem has existed forever.

Check the following for one of the more interesting and informative threads on the subject. It also is of interest because it turns out Excel DOES in fact use far more than 15 digits in its calculations and therefore could provide some things we just wish for though that is an unmentioned idea as the poster of interest sticks to his topic.

And the MS Support topic referenced, still available,:

(Sigh...) So integer math (if you're over, say, 45, you remember the Pentium issue) and floating point math are both flawed... at least "as implemented"... is Michio Kaku going to be doing a video explaining how the uncertainty principle somehow applies to mathematics? Why not, right? Used to be if a theory (theories only explain enough to help predict results, rather than describe reality) began to not match reality, that meant its limits were being found and a new theory was needed. Now it is taken to mean that reality is actually flawed because the theory is perfect.

So yeah, math must be flawed, so MS is not off here, it is our expectations that are flawed.

Anyway, the posts are interesting, and so is the Support article.

2017-08-02 16:27:33


Regarding John's comment below,
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


If you only need to know if the number if divisible by 2, using WorksheetFunction.IsEven is a lot simpler....

2015-11-23 02:02:33


Mod[23100001180000012345142800, 97]=18

2015-04-23 09:44:00



evaluates "12.4"

Excel 2013 on Windows 7 32-bit

2014-12-22 03:32:40


The Microsoft solution (=number - (int(number / divisor) * divisor) still has size limitations. A quick test showed it will fall over at 2^50 mod 11. It seems Excel is simply not up to big number maths.

2014-02-11 10:18:05


I have tried with your approach sir.but i am not able to find the solution
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


I have tried with your approach sir.but i am not able to find the solution
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

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.

Newest Tips

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

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.