Any hacks to overcome Excel's 15 digit limitation?

[RyanTG]

Are there any solutions for Excel's 15 digit limitation when making calculations?

Excel uses 15 digit floating point arithmetic. For example, if I type a 16 digit number into a cell, it will take the 16th digit and change it to a zero.

I have yet to find any straight forward answers to this problem on other forums so your response is highly valuable. Thanks for your help!

 

[mikerickson]

No. There isn't a straight forward way to overcome that limitation.

You might use UDF's like Plus in a worksheet formula
=Plus("12345678987654321", "5678987654321234")

Function Plus(aNumeral As String, bNumeral As String) As String
    Dim i As Long, Carry As Long, subSum As Long
    aNumeral = String(Len(bNumeral), "0") & aNumeral
    bNumeral = String(Len(aNumeral) - Len(bNumeral), "0") + bNumeral
    
    For i = Len(aNumeral) To 1 Step -1
        subSum = Val(Mid(aNumeral, i, 1)) + Val(Mid(bNumeral, i, 1)) + Carry
        Plus = (subSum Mod 10) & Plus
        Carry = Int(subSum / 10)
    Next i
    Do Until Left(Plus, 1) <> "0"
       Plus = Mid(Plus, 2)
    Loop
End Function

But Multiplication and Divison would be slow

 

[Rick Rothstein]

No. There isn't a straight forward way to overcome that limitation.

You might use UDF's like Plus in a worksheet formula
=Plus("12345678987654321", "5678987654321234")

Function Plus(aNumeral As String, bNumeral As String) As String
    Dim i As Long, Carry As Long, subSum As Long
    aNumeral = String(Len(bNumeral), "0") & aNumeral
    bNumeral = String(Len(aNumeral) - Len(bNumeral), "0") + bNumeral
    
    For i = Len(aNumeral) To 1 Step -1
        subSum = Val(Mid(aNumeral, i, 1)) + Val(Mid(bNumeral, i, 1)) + Carry
        Plus = (subSum Mod 10) & Plus
        Carry = Int(subSum / 10)
    Next i
    Do Until Left(Plus, 1) <> "0"
       Plus = Mid(Plus, 2)
    Loop
End Function

But Multiplication and Divison would be slow

Assuming 28 digit maximum numbers, your function can be simplified (no loop) like this (28 significant digits maximum for either argument)...

[table="width: 500"]
[tr]
	[td]Function Plus(Number1 As String, Number2 As String) As String
  On Error GoTo CalcError
  Plus = CDec(Number1) + CDec(Number2)
  Exit Function
CalcError:
  MsgBox "These numbers attempt to create a number that is out of range for this function."
End Function[/td]
[/tr]
[/table]

We can do a similar thing for Multiplication and Division as well, although there are size limits to the arguments and answers (maximum, 28 significant digits max for each of them)...

[table="width: 500"]
[tr]
	[td]Function Multiply(Number1 As String, Number2 As String) As String
  On Error GoTo CalcError
  Multiply = CDec(Number1) * CDec(Number2)
  Exit Function
CalcError:
  MsgBox "These numbers attempt to create a number that is out of range for this function."
End Function[/td]
[/tr]
[/table]

[table="width: 500"]
[tr]
	[td]Function Divide(Numerator As String, Denominator As String) As String
  On Error GoTo CalcError
  Divide = CDec(Numerator) / CDec(Denominator)
  Exit Function
CalcError:
  MsgBox "These numbers attempt to create a number that is out of range for this function."
End Function[/td]
[/tr]
[/table]

Examples
---------------------------
=Plus("1234567890123456789012345", "72654389001652349100284589")
=Multiply("12345678901234567", "123456789012")
=Divide("12345678901234567890", "98765432109876543210")

 

[mikerickson]

I get a Sub or Function Not defined error on the CDec function. (Mac Excel 2011 is the reason I suspect)

 

[Rick Rothstein]

I get a Sub or Function Not defined error on the CDec function. (Mac Excel 2011 is the reason I suspect)

Hmm, CDec is a VB function (goes back to the compiled days of VB)... I am surprised to find it is tied to PCs only.

 

[RyanTG]

You guys are brilliant!

Is it possible to incorporate the MAX function of an array of numbers into this code?

Thanks!

 

[Rick Rothstein]

Is it possible to incorporate the MAX function of an array of numbers into this code?

If what I posted in Message #3 works for you, then you can use this function (same 28 significant digit maximum restriction, per value, as in my other posting)...

Function BigMax(ParamArray Numbers()) As Variant
  Dim X As Long
  BigMax = CDec(Numbers(LBound(Numbers)))
  For X = LBound(Numbers) + 1 To UBound(Numbers)
    If CDec(Numbers(X)) > BigMax Then BigMax = CDec(Numbers(X))
  Next
End Function

 

[RyanTG]

The problem I am running into, is that excel has stored these 19 digit numbers with an apostrophe in the front. For example: '1732848000001023001.

I have tried many formulas to loop through column A to convert this text into a number. Unfortunately, excel then turns my number into 1732848000001020000.

How can I apply your code in this case?

 

[Rick Rothstein]

The problem I am running into, is that excel has stored these 19 digit numbers with an apostrophe in the front. For example: '1732848000001023001.

I have tried many formulas to loop through column A to convert this text into a number. Unfortunately, excel then turns my number into 1732848000001020000.

How can I apply your code in this case?

You cannot remove the leading apostrophe as that is what keeps the number as a text string. As for my functions, you do not, actually you should not, try to change them to real numbers... leave them as text string... that is what my function is expecting them to be. Just pass the cell reference into my functions as is (do not try to modify them) and the functions will do the rest.

 

[RyanTG]

Thank you! After pasting your BigMax code in a module and using the formula on my sheet, I am receiving a "#Value!" error that I can't seem to resolve.

Here it is:

Book1
	A
27669	1732848000000268468
27670	1732848000000268467
27671	1732848000000268466
27672	1732848000001020001
27673	#VALUE!
Contacts

Cell Formulas
Range	Formula
A27673	=BigMax(A27668:A27672)

P.S. The video in your signature helped me finally learn how to use MrExcel HTML Maker. Much thanks!!!