Search code examples
excelnumbersmodulus

Excel: calculate modulus of a very large number without getting overflow error

I have a A1 with 2 and a cell A2 with 288. I want to calculate MOD(2^288;2017) but this gives the NUM error.

I also tried using this formula: =number-(INT(number/divisor)*divisor) but this gives 0 as a result when the numbers are too big.

EDIT: not completely duplicate (see my answer for the function in excel), I used this algorithm: How to calculate modulus of large numbers?

Solution

  • Excel might be forcing an integer context on your values. Even an Int64 is not sufficient to handle numbers that large.

    You might need a custom VBA function to handle that. If you cast the inputs and outputs as doubles and then brute-forcing the modulus functionality, it should be capable of doing the math.

    My problem would be validating the output... I have no idea if this is the right value or not.

    Public Function BigMod(ByVal numerator As Double, ByVal denominator As Double) As Double

  Dim intval As Double
  intval = Int(numerator / denominator)

  BigMod = numerator - intval * denominator

End Function

    This works on values larger than Mod will handle, but does eventually break.

    For instance, 3 followed by 5 sets of 0's will break Mod. 3 followed by 6 sets of 0's will break BigMod.