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!

 

[RyanTG]

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

Is possible to make this formula handle n numbers rather than 2?

 

[Rick Rothstein]

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.
[/I]

Sorry, I had set the function up to take a comma delimited list of individual text string (composed of all digits). Here is the function revised to accept a range of cells instead...

Function BigMax(Rng As Range) As Variant
  Dim R As Range
  BigMax = CDec(Rng(1).Value)
  For Each R In Rng
    If CDec(R) > BigMax Then BigMax = CDec(R)
  Next
  BigMax = CStr(BigMax)
End Function

 

[RyanTG]

Sorry, I had set the function up to take a comma delimited list of individual text string (composed of all digits). Here is the function revised to accept a range of cells instead...

Function BigMax(Rng As Range) As Variant
  Dim R As Range
  BigMax = CDec(Rng(1).Value)
  For Each R In Rng
    If CDec(R) > BigMax Then BigMax = CDec(R)
  Next
  BigMax = CStr(BigMax)
End Function

That works perfectly! Sounds simple, but the final piece of this puzzle is to add 1 to the number.

Here is what I have:

eRow = Sheet4.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


    Cells(eRow, 1) = "=BigMax(Contacts27[CONTACTID]) + 1"

As soon as I do this, excel reverts to its old ways and converts the cell into a scientific number:

	A
1.73285E+18

</colgroup> <colgroup><col style="BACKGROUND-COLOR: #dae7f5" width="25"></colgroup> <colgroup><col></colgroup> <thead>
</thead> <tbody>
[TD="align: center"]27673[/TD]

</tbody>

 

[Rick Rothstein]

That works perfectly! Sounds simple, but the final piece of this puzzle is to add 1 to the number.

Here is what I have:

eRow = Sheet4.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


    Cells(eRow, 1) = "=BigMax(Contacts27[CONTACTID]) + 1"

As soon as I do this, excel reverts to its old ways and converts the cell into a scientific number:

	A
1.73285E+18

</colgroup> <colgroup><col style="BACKGROUND-COLOR: #dae7f5" width="25"></colgroup> <colgroup><col></colgroup> <thead>
</thead> <tbody>
[TD="align: center"]27673[/TD]

</tbody>

That is because adding 1 to the text string the function returns converts the text string to a real number and it is that real number that is assigned to your cell... when Excel sees a real number, it applies its "15-digit max" rule to it. The key to making this work is to add the one inside the function itself so that all Excel ever sees is a text string being assigned to a cell. Here is the function modified to do that (note the function name change to better reflect what the function is doing)...

Function MaxPlusOne(Rng As Range) As Variant
  Dim R As Range
  MaxPlusOne = CDec(Rng(1).Value)
  For Each R In Rng
    If CDec(R) > MaxPlusOne Then MaxPlusOne = CDec(R)
  Next
  MaxPlusOne = CStr(MaxPlusOne + 1)
End Function

 

[RyanTG]

When I include your formula in the table, it shows the formula instead of the calculation. When I include it outside the table, it shows the calculation.

	A
1732848000001020001
=MaxPlusOne(Contacts27[CONTACTID])

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]27672[/TD]

[TD="align: center"]27673[/TD]

</tbody>

I have tried playing with the formats to no avail. Thank you again for all your brilliant help!

 

[Rick Rothstein]

When I include your formula in the table, it shows the formula instead of the calculation. When I include it outside the table, it shows the calculation.

	A
1732848000001020001
=MaxPlusOne(Contacts27[CONTACTID])

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]27672[/TD]

[TD="align: center"]27673[/TD]

</tbody>

I have tried playing with the formats to no avail. Thank you again for all your brilliant help!

The only thing I can think of is the cell you are putting it in is formatted as Text. If I am right, simply changing the cell to General and reentering the formula should make it work as expected.

 

[RyanTG]

The only thing I can think of is the cell you are putting it in is formatted as Text. If I am right, simply changing the cell to General and reentering the formula should make it work as expected.

Phenomenal!

Thank you for your help Rick!