I need to make calculations with numbers of at least 40 characteres

[corinthia]

Hi all
I need to make calculations with numbers of at least 40 characteres using Excel97, but whenever I try to input a number as long as 30 or 40, it changes the number, i.e.
I input
12345678910123456789101234567891012345678910
Excel changes to
12345678910123400000000000000000000000000000.00

I do not want to use the Text Format, because even thought it goes to show the number as it is exactly, i cannot use a formula afterwards.

In all, I just want to be able to make simple math operations using long numbers.

Any ideas anyone?

 

[GlennUK]

The simple answer is that you can't. Excel uses approx. 15 significant digits for it's calculations.

Can you tell us what you are trying to do with such large numbers, and why?

Glenn.

 

[corinthia]

Well just trying to use a formula to generate an IBAN (International Bank Account Number)

But I'll see to a different way of doing it

 

[ScorchedLemonade]

This will add 1. You need to have the destination already formatted as text in some cases (like for 9999).
Anyway, it could be run a few times to add other positive integers.

Multiplication is probably best tackled using a convolution type method. If I get time later I'll try and indicate a method for this.
Subtraction can be done similarly also - but checking for the last non-zero digit. If all the numbers are exactly 40 digits then the following can be simplified (to take out the cases where all digits are 9) (and similarly for subtraction the 100...0 case).

Function Succ(x As String) As String
y = Len(x)
Flag = 0
For i = y To 1 Step -1
If Mid$(x, i, 1) <> 9 Then
Flag = i
i = 1
End If
Next i
If Flag = 0 Then
x = &quot;1&quot; & x
For i = 2 To y + 1
Mid$(x, i, 1) = &quot;0&quot;
Next i
Else
For i = Flag + 1 To y
Mid$(x, i, 1) = &quot;0&quot;
Next i
Mid$(x, Flag, 1) = Mid$(x, Flag, 1) + 1
End If
Succ = x
End Function

 

[ScorchedLemonade]

I was slightly remiss in not getting back so far on multiplication. There's a technique of multiplication based on convolution and a macro could be based on this, pulling out individual characters.
Essentially, it works like this (longer strings can be handled similarly so I'll just use a shorter example).
To get:
8752
x2346
20532192

2*6=12 (put down the 2 and carry the 1)
5*6+4*2+1 (carried)=39 (put down the 2 and carry the 3)
7*6+5*4+2*3+3 (carried)=71 (put down the 1 and carry the 7)
6*8+7*4+5*3+2*2+7 (carried) =102 (put down the 2 and carry 10)
8*4+7*3+5*2+10 (carried) = 73 (put down the 3 and carry the 7)
8*3+7*2+7 (carried) = 45 (put down the 5 and carry the 4)
8*2+4 (carried)=20 (put down the 0 and carry the 2)
2 (carried) =2 (put down the 2)

It's possible to do things like 52*46 first then 87*46+52*23+carry and then 87*23+(carry) but that might be harder with strings.

If the strings are of unequal length you can fill the left of the shorter one with zeros.

e.g. 215*7

215
x007
1505

Basically what you do is take a digit from the first number in place k and a digit from the next in place l, where k+l is constant. Multiply them and add them together. Then add the carry - this will give you (something)*10^(k+l), if k,l are counted from the right starting at 0.

So the schematic would be for strings A and B of length r and s to make them both the same length t=max(r,s) by filling the left up with 0s if necessary. Then something like this (I'm writing informally, as I don't know the code for all of that which I will describe, so some of it may need to be altered to work):

Product=&quot;&quot; ' e.g. I'm not sure you can start like this
' I'm just after making an empty string to start
Carry=0
For i=0 to 2t
x=Carry
For j=0 to i
x=x+Mid$(A,t-j,1)*Mid$(B,i-j)
' and above you'd need to be able to change the
'strings to numbers first
Next j
Carry=x-(x Mod 10)
y=x Mod 10
'then y would have to be changed into a string
Product=y&Product ' not sure of the syntax here but I
' mean to concatenate y to the front of
' Product
Next i
'then you'd need to convert the final carry into a string
'and stick it on the front too
'then there could be code to chop off any leading zeros

 

[bluedragon2]

Can you break the number up into 4 columns and make your calculations that way?

 

[Dreamboat]

Corinthia: What kind of math? Is it as simple as scorched seems to think? You want to add one or two digits? Consider breaking your numbers down into 4 cells, 10 numbers in each cell, and performing calculations that way. Then, in a fifth cell, you could place your &quot;final&quot; number by concatenating the other four:

=A2&B2&C2&D2

Anne Troy
Way cool stuff:

http://www.cafeshops.com/OfficeExperts

 

[ScorchedLemonade]

I couldn't get things like d=c&d to work, but I was able to use Join. Here's a multiplication utility.
It should be good for fairly large numbers - eventually it would break down but I think it would be good to multiplicand and multiplier each around half a million digits. Addition is much simpler and should be fairly straightforward using similar techniques (chop the number into parts small enough so that Excel can add them together and take across a carry - the carry would never exceed 1 for addition of two numbers).

Function ConvolutionMultiply(a As String, b As String) As String
k = Len(a)
l = Len(b)
d = &quot;&quot;
If k < l Then m = l Else m = k
If m Mod 5 = 0 Then Else m = m + 5 - (m Mod 5)
Do While Len(a) < m
a = &quot;0&quot; & a
Loop
Do While Len(b) < m
b = &quot;0&quot; & b
Loop
n = m / 5
carry = 0
For i = 1 To n
c = 0
For j = 1 To i
c = c + Left$(Right$(a, 5 * j), 5) * Left$(Right$(b, (i + 1 - j) * 5), 5)
Next j
c = c + carry
carry = Left$(c, Len(c) - 5)
c = Right$(c, 5)
d = Join(Array(c, d), &quot;&quot
Next i
For i = n - 1 To 1 Step -1
c = 0
For j = 1 To i
c = c + Left$(Right$(Left$(a, 5 * i), 5 * j), 5) * Left$(Right$(Left$(b, 5 * i), (i + 1 - j) * 5), 5)
Next j
c = c + carry
carry = Left$(c, Len(c) - 5)
c = Right$(c, 5)
d = Join(Array(c, d), &quot;&quot
Next i
d = Join(Array(carry, d), &quot;&quot
Do While Left$(d, 1) = &quot;0&quot;
d = Right$(d, Len(d) - 1)
Loop
ConvolutionMultiply = d
End Function

 

[ScorchedLemonade]

Sorry, the two lines

carry = Left$(c, Len(c) - 5)

should read

If Len(c) > 5 Then carry = Left$(c, Len(c) - 5) Else carry = 0

 

[ScorchedLemonade]

Addition - it's not done by convolution but I put a C in front anyway as Sum is already used

Function CSum(a As String, b As String) As String
k = Len(a)
l = Len(b)
d = &quot;&quot;
If k < l Then m = l Else m = k
Do While Len(a) < m
a = &quot;0&quot; & a
Loop
Do While Len(b) < m
b = &quot;0&quot; & b
Loop
carry = 0
For i = m To 1 Step -1
c = Right$(Left$(a, i), 1) * 1 + Right$(Left$(b, i), 1) * 1 + carry 'Had to multiply by 1 here otherwise it concatenated rather than added
If c > 9 Then carry = 1 Else carry = 0
d = Join(Array(Right$(c, 1), d), &quot;&quot
Next i
If carry = 1 Then d = Join(Array(&quot;1&quot;, d), &quot;&quot
CSum = d
End Function