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?
To solve this problem add this function in excel: alt+f11 -> module -> add and use BigMod(2;288;2017) if you want to calculate 2^288 mod 2017
Public Function BigMod(ByVal grondgetal As Double, ByVal exponent As Integer, ByVal modgetal As Double) As Double
Dim hulp As Integer
hulp = 1
Dim i As Integer
For i = 1 To exponent
hulp = hulp * grondgetal
hulp = hulp - Int(hulp / modgetal) * modgetal
Next i
BigMod = hulp
End Function
I needed it to work with a very large number. The example above works if i take 3 followed by 7 sections of 0's, but breaks if I keep going.
So I worked out how I would do it on paper and wrote a function that will handle it that way. Since it only works with one section at a time (plus the remainder from the prior section) it is never working with a value larger than 1 million.
Public Function AnyMod(ByVal numerator As String, ByVal denominator As Double) As Double
' inch worm through the numerator working one section at a time to get the remainder given any size string
Dim numericalDivider As String
Dim decimalDivider As String
Dim sectionLength As Integer
' in the US
numericalDivider = ","
decimalDivider = "."
sectionLength = 3
' in Europe
'numericalDivider = "."
'decimalDivider = ","
'sectionLength = 3
' input cleanup - replace numerical section divider
numerator = Replace(numerator, numericalDivider, "")
' input cleanup - chop any decimal off of it
If (InStr(1, numerator, decimalDivider)) Then
numerator = Left(numerator, InStr(1, numerator, decimalDivider) - 1)
End If
Dim pos As Integer ' the next position in numerator to be read
Dim remainder As Double ' the remainder of the last division
Dim subs As String ' the current section being read
Dim length As Integer ' the total length of numerator
Dim current As Double ' the current value being worked on
Dim firstSegment As Integer ' the length of the first piece
'set up starting values
length = Len(numerator)
firstSegment = length Mod sectionLength
If firstSegment = 0 Then firstSegment = sectionLength
remainder = 0
pos = 1
'handle the first section
subs = Mid(numerator, pos, firstSegment)
pos = pos + firstSegment
' handle all of the middle sections
While pos < length
' work with the current section
' (10 ^ sectionLength) is 1000 when sectionLength is 3. basically shifting the decimal
current = remainder * (10 ^ sectionLength) + subs
remainder = current Mod denominator
' determine the next section
subs = Mid(numerator, pos, sectionLength)
pos = pos + sectionLength
Wend
' handle the last section
current = remainder * (10 ^ sectionLength) + subs
remainder = current Mod denominator
' return the response
AnyMod = remainder
End Function
Call it like =AnyMod(Text(A1, "0"), 2017)
In your case you'd need to get the value of 2^288, but this works if you have a large value in a cell and want to get a remainder.
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.
