Anyway to type more than 15 digits for calculation?

[Vedud]

Hello, I am new on this platform. for a while, I am searching for this issue. İs it possible to calculate numbers more than 15 digits even Vba included ?

 

[herofox]

hi ,
You had to upload a simple file as desired
But I did an example for you with two methods of solving ... one with a simple equation and the other with a knowledge function SumPro
and it must to add this code for Function

Function SumPro(Num1 As String, Num2 As String) As String
Dim i%, Mx%, Mn%, Output$, Result$, Comment$, Dec$
    Num1 = Format(Num1, IIf(InStr(Num1, "E"), "#", "@")): Num2 = Format(Num2, IIf(InStr(Num2, "E"), "#", "@"))
    Dec = Val(Mid(Num1, InStr(Format(Num1, "#."), "."))) + Val(Mid(Num2, InStr(Format(Num2, "#."), ".")))
    Num1 = StrReverse(Mid(Num1, 1, InStr(Format(Num1, "#."), ".") - 1)): Num2 = StrReverse(Mid(Num2, 1, InStr(Format(Num2, "#."), ".") - 1))
    If Len(Num1) > Len(Num2) Then Mx = Len(Num1): Mn = Len(Num2) Else Mx = Len(Num2): Mn = Len(Num1)
    GoSub Deci:
        If Dec >= 1 Then Num1 = Result: Num2 = Int(Dec): Result = "": Mx = 1: Mn = 1: Dec = Dec - 1: GoSub Deci
        If Dec > 0 Then Result = Result & Mid(Dec, 2)
        SumPro = Result
    Exit Function
Deci:
        For i = 1 To Mx
            If Comment = "" And i > Mn Then Exit For
            Output = Val(Mid(Num1, i, 1)) + Val(Mid(Num2, i, 1)) + Val(Comment)
            Result = Right(Output, 1) & Result
            Comment = Mid(StrReverse(Output), 2)
        Next i
    Result = StrReverse(Mid(Num1, i)) & StrReverse(Mid(Num2, i)) & Result
Return
End Function

and excel sheet work as well ...please check ,& for this Reason i said to you Upload asimple File to Don't lost any time More like Now

 

[Vedud]

hi ,
You had to upload a simple file as desired
But I did an example for you with two methods of solving ... one with a simple equation and the other with a knowledge function SumPro

Hi again I downloaded the file .Maybe I didn't get well, When I add a number between 15 digits ,it puts 0 at the end.

 

[Vedud]

check please

 

[Peter Bartholomew]

Did you enter the so-called 'numbers' as text rather than allowing Excel to convert it to (the binary representation) a number? The cell should have a number format of 'text' or prefixing it with an apostrophe could work.

 

[Vedud]

Did you enter the so-called 'numbers' as text rather than allowing Excel to convert it to (the binary representation) a number? The cell should have a number format of 'text' or prefixing it with an apostrophe could work.

Hi, Yes I tried both ways using text format or an apostrophe. It lets us to see whole numbers more than 15 but I can't calculate things with it. I searched for this issue. Most of the Sites say it is impossible to cause of excel only lets 15 digits to calculate. It doesn't let scientific calculation

 

[Peter Bartholomew]

I have tested the UDF, starting with things like
= SumPro(X,Y)
where X referred to 19.46 and Y referred to 213 and it worked fine, as did a summation involving a 30 digit string.

As long as all your calculations involve the addition of positive numbers, you are set to go!

 

[Vedud]

I should thank both of you. It was so important for me. I will sleep happily.

 

[Vedud]

I was just focusing on typing more than 15 digits in a box. İndirectly like SumPro I can do that

 

[Peter Bartholomew]

@herofox
Did you write the routine specifically for this discussion? I had rather assumed that it was something you had collected but now I am not so sure.
I tried to modify the routine to perform the addition of long binary numbers (by changing the numbers 0,1,2,3 obtained by addition to their two-character binary representations) but found that the leading 1 (carried forward) was missed when the two binary numbers were the same length.
I then tried this with the original routine and found it gave the same thing, e.g. 97+15=12

My fix was to allow the 'Deci:' loop to be

For i = 1 To Mx + 1
    If Comment = "" And i > Mn Then Exit For
    Output = Val(Mid(Num1, i, 1)) + Val(Mid(Num2, i, 1)) + Val(Comment)
    Result = Right(Output, 1) & Result
    Comment = Mid(StrReverse(Output), 2)
Next i

but this may have introduced errors in other cases?

 

[Vedud]

Hello, I have a question. I wrote more than 15 digits in number format. But ıt doesn't let me calculate on it. Check yellow box, please

 

[Peter Bartholomew]

You can hold a string of digits [0-9] longer than 15 characters, but that doesn't make it a number. The perform calculations Excel either errors or ignores the least significant digits and runs the remaining floating point value through the calculation. To perform calculations to greater accuracy you would need a different calculation process, either using 64 bit numbers or writing your own code for each mathematical operation.