A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Mod Function



 
 
Thread Tools Display Modes
  #1  
Old November 29th, 2007, 03:34 PM posted to microsoft.public.excel.worksheet.functions
Diogo
external usenet poster
 
Posts: 38
Default 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
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.
  #2  
Old November 29th, 2007, 03:47 PM posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
external usenet poster
 
Posts: 3,232
Default Mod Function

It's a well known bug

http://support.microsoft.com/default...b;en-us;119083



--


Regards,


Peo Sjoblom


"Diogo" wrote in message
...
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.



  #3  
Old November 29th, 2007, 03:58 PM posted to microsoft.public.excel.worksheet.functions
Diogo
external usenet poster
 
Posts: 38
Default Mod Function

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?
  #4  
Old November 29th, 2007, 06:58 PM posted to microsoft.public.excel.worksheet.functions
Dana DeLouis
external usenet poster
 
Posts: 468
Default Mod Function

... = (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 he

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

--
Good Luck
HTH
Dana DeLouis


"Diogo" wrote in message
...
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?


  #5  
Old November 30th, 2007, 11:24 AM posted to microsoft.public.excel.worksheet.functions
Diogo
external usenet poster
 
Posts: 38
Default Mod Function

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.
  #6  
Old November 30th, 2007, 05:00 PM posted to microsoft.public.excel.worksheet.functions
Diogo
external usenet poster
 
Posts: 38
Default Mod Function

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.
  #7  
Old December 1st, 2007, 02:18 PM posted to microsoft.public.excel.worksheet.functions
Jerry W. Lewis
external usenet poster
 
Posts: 491
Default Mod Function

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

"Diogo" wrote:

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

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

  #8  
Old December 1st, 2007, 11:27 PM posted to microsoft.public.excel.worksheet.functions
Dana DeLouis
external usenet poster
 
Posts: 468
Default Mod 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 ...


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_Co...ementation.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.

--
HTH :)
Dana DeLouis
Windows XP & Excel 2007


"Diogo" wrote in message
...
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.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:20 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.