how can I sum up the values with more than 15 significant digits?

[Guest]

how can I sum up the values with more than 15 significant digits?

 

[David Biddulph]

I would use LEFT and RIGHT to split the text strings with the long numbers
in them (with some extra complication to split in appropriate places if the
inputs aren't integers), then add the separate parts, then concatenate the
results.

 

[Ron Rosenfeld]

how can I sum up the values with more than 15 significant digits?

Native Excel will only handle fifteen digits. You could use VBA if your values
are in the range allowed for the Decimal data type. That range is described
as:

+/-79,228,162,514,264,337,593,543,950,335 for zero-scaled numbers, that is,
numbers with no decimal places. For numbers with 28 decimal places, the range
is
+/-7.9228162514264337593543950335. The smallest possible non-zero number is
0.0000000000000000000000000001.

A UDF that would allow you to do this is:

==========================
Function SumLongNum(rg As Range) As String
Dim temp As Variant
Dim c As Range

For Each c In rg
temp = CDec(c.Text) + temp
Next c
SumLongNum = temp
End Function
============================

You MUST enter any values that are more than 15 digits as TEXT -- probably by
preceding your entry with a single quote.

Also, the output of the routine, as written, will be TEXT. However you could
add a test for the size, and output a Double if it is small enough.

Also, you could add a routine to format the value with commas

An example of the output

A1: 1E20
A2: 72

=sumlongnum(A1:A2) -->
100000000000000000072

================================
Function SumLongNum(rg As Range, Optional Commas As Boolean = True) As String
Dim temp As Variant
Dim c As Range
Dim Pos As Long

For Each c In rg
temp = CDec(c.Text) + temp
Next c

'Format with commas
'Start at end or at decimal

If Commas = True Then
Pos = IIf(InStr(temp, ".") = 0, Len(temp), InStr(temp, ".") - 1)
Do
temp = Left(temp, Pos - 3) & "," & Right(temp, Len(temp) - Pos + 3)
Pos = Pos - 3
Loop Until Pos < 4
End If

SumLongNum = temp
End Function
==================================

--ron

 

[Bernard Liengme]

Try the free add-in from
http://digilander.libero.it/foxes/SoftwareDownload.htm
best wishes

 

[Guest]

Try using Windows calculator for up to 32 digits of accuracy. Format cells as
text then enter the values as text in Excel.

The following macro sums a selection of values in windows calculator and
copies the result to the clipboard. To enter the answer in a cell, format a
cell as text and then choose Edit Paste.

Sub SumValsWithCalc()

Dim ReturnValue, I
ReturnValue = Shell("CALC.EXE", 1) ' Run Calculator.
AppActivate ReturnValue ' Activate the Calculator.
For Each I In Selection
SendKeys I & "{+}", True ' Send keystrokes to Calculator
Next I ' to add each value of I.
SendKeys "^C%{F4}", True ' Send ALT+F4 to close Calculator.

End Sub

 

[David McRitchie]

and just for completeness of Ron's instructions, format the cell
as right justified. Guess your problem now is to select
from the three complete solutions.