Mod Function

[Diogo]

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

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.

 

[Peo Sjoblom]

It's a well known bug

http://support.microsoft.com/default.aspx?scid=kb;en-us;119083



--


Regards,


Peo Sjoblom

 

[Diogo]

Update:

Using:

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
#VALUE!

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
10^x
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
name POWERMOD.
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)
14

So, given that, these are easy.

'Mod(10^29,97)
Debug.Print PowerMod(10, 29, 97)

'Mod(10^33,97)
Debug.Print PowerMod(10, 33, 97)

'Mod(10^999,97)
Debug.Print PowerMod(10, 999, 97)

Returns:
57
28
77

 

[Diogo]

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
Loop
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
'
Const MAXLONG = &H7FFFFFFF
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)
Else
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)
Else
A = (A + A) Mod M
End If
B = B \ 2
Wend
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
thoughts?
Thanks.

 

[Diogo]

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

Right???????????

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)
right??????

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.

 

[Jerry W. Lewis]

If you are still having difficulties with the calculations, consider
bypassing the internal pecision limitations.

A couple of Excel add-ins that have mod functions and support user specified
numeric precision are
http://digilander.libero.it/foxes/index.htm
http://precisioncalc.com/

You also might consider the free algibraic calculator Maxima
http://maxima.sourceforge.net/

Jerry

 

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

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

Hence
=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...
http://www.pangaliit.ee/files/eng_Codes/implementation.pdf

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.