Mod Function



OK, need help with the following:

Mod(10e+11;97) returns #NUM!, anything higher it blows.

But if I use Mod(n;d)=n-d*INT(n/d) I can go until Mod(10e+15;97), anything
higher it returns increasingly lower negative values instead of the real

My doubt is: If I can calculate this values in the windows calculator why
can't Excel do the same? In the windows calculator I was able to calculate
Mod(10e+32;97), and I suspect i could go higher. Could someone help?

Thanks in advanced.




Function MyMod(n, m)
MyMod = (CDec(n) - m * Int(CDec(n) / m))
End Function

I was able to go as far as mod(10e28;97), anything higher and it returns

I need to go as far as 10e32, almost there :)

Any thoughts?

Dana DeLouis

... = (CDec(n) - m * Int(CDec(n) / m))
I was able to go as far as mod(10e28;97),

Hi Diogo. :>) You are so close. A favorite subject of mine. You may
find this of interest.
For your stated problem, your large first number is written in the form of
When an intermediate number is large (ie 10^32), there is a Number Theory
procedure that is much more efficient by avoiding such a large calculation.
Problems of the form Mod(n^m,x) are usually not calculated like this
directly, but are usually calculated by a function that usually goes by the
If you do an internet search, you should be able to find a number of
different techniques.

To give you some idea, the following calculation would be hard.

81703395 ^ 81703395

It has over 646,000,000 digits in the answer.

Given that, who knows how astronomically large the first number is here:

Mod(123456789012345678901234567 ^ 123456789012345678901234567, 97)

However, Excel can calculate this easily and quickly.
(Mine has been modified to do these types of calculations.)

given that n = 123456789012345678901234567
calculate Mod(n^n,97)

?PowerMod(n, n, 97)

So, given that, these are easy.

Debug.Print PowerMod(10, 29, 97)

Debug.Print PowerMod(10, 33, 97)

Debug.Print PowerMod(10, 999, 97)



Dana, thanks you putted me in the right direction, found this code on the net.

Function PowerMod(ByVal B As Long, ByVal X As Long, ByVal N As Long) As Long
' Dr Memory's "PowerMOD" function (Nov 2003)
' Returns B ^ X mod N
' 100% VB, no API, no DLL, and no Overflow
' Superfast, only 1 iteration for each BIT in X
' (i.e. max 31 iterations!).
' Valid for all 0 < N,X,B < &H7FFFFFFF = 2 ^ 32 - 1
' < 2,147,483,647
' Method:
' Binary Decomposition/Residual of the Exponent
Dim K As Long
Dim BX2N As Long
K = 1
BX2N = B ' B^1

Do While X > 0
If X Mod 2 Then K = MulMod32(BX2N, K, N) ' K = (BX2N * K) mod N
BX2N = MulMod32(BX2N, BX2N, N) ' BX2N = (BX2N ^ 2) mod N
X = X \ 2
PowerMod = K ' that's all, folks!
End Function

Function MulMod32(ByVal A As Long, ByVal B As Long, ByVal M As Long)
' return A * B mod M without risking overflow
Dim MM As Long
A = A Mod M
While B > 0
If (B Mod 2) = 1 Then
If A > MAXLONG - MM Then ' (A + MM) Mod M will overflow
If A <= MM Then MM = A - (M - MM) Else MM = MM - (M - A)
MM = (A + MM) Mod M ' it's safe
End If
End If

If A > MAXLONG - A Then ' ditto for 2*A mod M
A = A - (M - A)
A = (A + A) Mod M
End If
B = B \ 2
MulMod32 = MM
End Function

This works good for numbers that are easly decomposed in 10th powers, but
what about strange numbers like mod(13523456273456;97). It's difficult to
decompose it without loosing significant decimal places along the way. Any


Dana I've found in the net another forum where you posted some questions
about mod97.
I'm having trouble understanding how it all works. I thought I had it all
figured out but....
I do not understand the mechanics of the check digit ISO 7064, MOD 97-10.
Suppose you have this number for a bank check:

00020001 01234567890 12345678CD 12

Where CD are the two check digit numbers

Suppose I want to calculate the two control numbers: CD

I apply the algorithm ISO 7064, MOD 97-10 and I come up with the number 35

So in the real bank check I should have a line that should be like this:

00020001 01234567890 1234567835 12


So in a real situation if I want to do a check digit on a real bank check, I
would pick up the all sequence, ignoring the two numbers before the last two
in this case (35), and I should apply the algorithm ISO 7064, MOD 97-10 and I
should come up with a number, that should be equal to 35 (the check digits)

Is this how it works?

I've been testing with real checks and I don’t come up the same numbers that
are supposed to be the check digits.

Help please.

Dana DeLouis

This works good for numbers that are easly decomposed in 10th powers, but
what about strange numbers like mod(13523456273456;97). It's difficult ...

Hi. A 14 digit number can be done more directly via your code you listed

Function MyMod(x, y)
MyMod = x - Int(x / y) * y
End Function

=MyMod(13523456273456,97) -> 8

I am not exactly sure what the second code (MulMod32) is for.
In your main code, you are using Excel's MOD function. For some unknown
reason, Microsoft refuses to fix this bug, despite being asked for years.
You may want to use your own MOD function from above instead.

(I'm still stuck on this part
BX2N = MulMod32(BX2N, BX2N, N)

It "appears" to me to be an error, but it does work. (If done this way, I
was expecting MulMod32(BX2N, 2, N) )
I'll have to study it some more. However, it gave me a great idea on my own
code. Thanks.

Anyway, Large numbers can be broken down into smaller steps. Here's an
example of a 37 digit number.
The idea here is that you Mod a smaller group of the numbers. You append
the results to the beginning of the next group of numbers.

Sub TestIt()
Dim n As String
Dim x As Long
n = "1234567890123456789012345678901234567"
x = sMod(n, 97)
Debug.Print x
'// Your example
x = sMod("13523456273456", 97)
Debug.Print x
End Sub

Function sMod(n As String, x As Double) As Double
'// Mod(N, x) where N is a Long string
Dim s As String
Dim z As String
Dim P As Long
Const Stp As Long = 7
z = vbNullString
For P = 1 To Len(n) Step Stp
s = z & Mid$(n, P, Stp)
z = CStr(CDbl(s) Mod x)
Next P
sMod = CDbl(z)
End Function

On your question of
Suppose you have this number for a bank check:
00020001 01234567890 12345678CD 12

I am not familiar with this, but the placement of the check digit appears to
be incorrect.
I was expecting it to be the last two digits.

There are lots of internet recourses, but here's one...

Does your data follow the pattern listed there?

For your Mod question, Steps 1.1 - 1.3 near the botton are following the
code above.
Step 1.2 is a little confusing.

What they are doing is taking 67 from step 1.1 and appending it to "6789012"
So, what they are actually doing in step 1.2 is Mod(676789012,97) ->30

Anyway, hope this is of some help. Good luck.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question