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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|