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

On Sun, 7 Oct 2007 03:04:09 -0700, mohammad
<mohammad@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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
.

Relevant Pages

  • RE: FileSearch to locate the latest (last saved) file
    ... created an array to put all this inrformation so you can perform a sort. ... Dim sReport As Workbook, sDashboard As Workbook ... Dim fLdr As String, Fil As String, FPath As String, x As String, _ ... FileDates= temp ...
    (microsoft.public.excel.programming)
  • RE: FileSearch to locate the latest (last saved) file
    ... Dim sReport As Workbook, sDashboard As Workbook ... Dim fLdr As String, Fil As String, FPath As String, x As String, _ ... FileDates= temp ... For i = 1 To NewestFile ...
    (microsoft.public.excel.programming)
  • RE: FileSearch to locate the latest (last saved) file
    ... Dim sReport As Workbook, sDashboard As Workbook ... Dim fLdr As String, Fil As String, FPath As String, x As String, _ ... FileDates= temp ... For i = 1 To NewestFile ...
    (microsoft.public.excel.programming)
  • Re: Checkboxes are duplicated over and over as emf in the Temp fol
    ... The filesystemobject has a folder.delete method, but if any file in a folder ... Dim cFilesToDelete As Collection ... you are doing it seems you can stay focused in the temp root. ... I created an app with some checkboxes. ...
    (microsoft.public.excel.programming)
  • Re: Problems with Bubble Sort on 2D Array
    ... Function BubbleSort2D(PassedArray As Variant, col As Long) ... Dim i As Integer, j As Integer, k As Integer ... Dim Temp As Variant ... ' Sorts an array using bubble sort algorithm in descending order using ...
    (microsoft.public.excel.programming)