Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how can I sum up the values with more than 15 significant digits?
how can I sum up the values with more than 15 significant digits?
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how can I sum up the values with more than 15 significant digits?
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. -- David Biddulph "mohammad" wrote in message ... how can I sum up the values with more than 15 significant digits? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how can I sum up the values with more than 15 significant digits?
On Sun, 7 Oct 2007 03:04:09 -0700, mohammad
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how can I sum up the values with more than 15 significant digits?
Try the free add-in from
http://digilander.libero.it/foxes/SoftwareDownload.htm best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "mohammad" wrote in message ... how can I sum up the values with more than 15 significant digits? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how can I sum up the values with more than 15 significant digits?
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 "mohammad" wrote: how can I sum up the values with more than 15 significant digits? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
how can I sum up the values with more than 15 significant digits?
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format significant digits | Excel Discussion (Misc queries) | |||
Significant digits | Excel Worksheet Functions | |||
significant digits for decimals | Excel Worksheet Functions | |||
return significant digits of a cell | Excel Worksheet Functions | |||
How do I increase the of significant digits given in the slope i. | Charts and Charting in Excel |