The MOD Function

This page describes the MOD function and an alternative to avoid errors with large numbers.

The MOD Worksheet Function

The worksheet function MOD takes two (integer) numbers and returns the remainder when the first number is divided by a some multiple of the second number. For example, =MOD(23,7) returns 2 because when 23 is divided by some multiple of 7 (in this case 3), the remainder is 2: (23-(7*3) = 2). In some earlier versions of Excel, with very large numbers, the MOD function can return a #NUM! error. If you need to use MOD with very large numbers (I encountered this error working on some prime number functions), you should replace it with an alternative formula. Instead of using =MOD(A1,A2) use the formula:

=A1-(INT(A1/A2)*A2)

This is not a bug that one you run into on a regular basis. It occurs only when you are using very large numbers. I stumbled across it while writing some formulas for Prime Numbers, an area where the number get very large very quickly. The error occurs under a very specific circumstance: if you have =MOD(Number,Divisor), you will get a #NUM! error if Divisor*134217728 is less than or equal to Number. For example, =MOD(940000000,7) will cause an error. This problem seems to have been fixed as of Excel 2007, but exists in Excel 2003. The alternative formula shown above will not cause an error in any verison of Excel.

The Mod VBA Operator

You can also get overflow errors in VBA using the Mod operator with very large numbers. For example,

    Dim Number As Double
    Dim Divisor As Double
    Dim Result As Double

    Number = 2 ^ 31
    Divisor = 7
    Result = Number Mod Divisor ' Overflow error here.
    

will cause an "Overflow Error" when using Mod. A VBA function that will prevent overflow errors is as follows:

    Function XMod(ByVal Number As Double, ByVal Divisor As Double) As Double
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' XMod
    ' Performs the same function as Mod but will not overflow
    ' with very large numbers. Both Mod and integer division ( \ )
    ' will overflow with very large numbers. XMod will not.
    ' Existing code like:
    '       Result = Number Mod Divisor
    ' should be changed to:
    '       Result = XMod(Number, Divisor)
    ' Input values that are not integers are truncated to integers. Negative
    ' numbers are converted to postive numbers.
    ' This can be used in VBA code and can be called directly from 
    ' a worksheet cell.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Number = Int(Abs(Number))
        Divisor = Int(Abs(Divisor))
        XMod = Number - (Int(Number / Divisor) * Divisor)
    End Function
    

If you call this with the same numbers in the previous example,
     Result = XMod(2 ^ 31,7)
you will not get an overflow error. You will get the correct result (2, in this case). I encountered this problem with a prime factorization procedure I wrote, where the prime factors were very, very large, so I wrote XMod function as a replacement for the Mod operator. If you have code like:
Result = Number Mod Divisor
You should change it to:
Result = XMod(Number,Divisor)

In addition to using this function in VBA, you can call it directly from a worksheet cell to replace the MOD function. This problem exists in all versions of Excel VBA, up to and including Excel 2013.