SUMing small numbers

[Kevin Burton]

For simplicty say I have two cells with values 0.0000004499190595611849428288
and 0.0000004499190595611849428288. If I have a formula that adds the two
cells (=A1 + A2) the value seems to be accurate. But if I use the 'SUM'
function (=SUM(A1:A2)) I get a result of zero, exactly. I have tried
formatting the result of the SUM with scientific notation just to see if the
value is just too small but it is really zero. I should see the sum to be
something like .0000009... but it is zero. This came up when I tried to SUM
about 31,000 data values (all small like this) and the result came up to be
zero. When I try to compute standard deviation or average I get a divide by
zero error, presumably because the SUM is zero. Any suggestions?

Thank you.

Kevin

 

[Don Guillett]

15 digit max

 

[Kevin Burton]

15 digits NOT 15 significant figures?

Anyway, even with 15 digits I should see the sum of .000000449919060 and
..000000449919060 which would not be zero. There is something else wrong.
Again if I simply add the cells it seems accurate. Using SUM or any of the
functions that rely on SUM (STDEV, VAR, etc.) result in bad results.

 

[Jim Cone]

Kevin,
Adding the two numbers with or without "Sum" gave the same result for me...
approx 0.0000009
I did format the data and formulas cells with a number format to 21 places
before starting.
You could be the victim of a cell formatted as "text".
The Sum function will ignore numbers formatted as text in cell references.

Also, regarding summing very small numbers, there is a floating point
truncation error that can cause the result to vary depending on the order
in which the numbers are totaled.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)





"Kevin Burton"
<[email protected]>
wrote in message
For simplicty say I have two cells with values 0.0000004499190595611849428288
and 0.0000004499190595611849428288. If I have a formula that adds the two
cells (=A1 + A2) the value seems to be accurate. But if I use the 'SUM'
function (=SUM(A1:A2)) I get a result of zero, exactly. I have tried
formatting the result of the SUM with scientific notation just to see if the
value is just too small but it is really zero. I should see the sum to be
something like .0000009... but it is zero. This came up when I tried to SUM
about 31,000 data values (all small like this) and the result came up to be
zero. When I try to compute standard deviation or average I get a divide by
zero error, presumably because the SUM is zero. Any suggestions?
Thank you.
Kevin

 

[Kevin Burton]

How did you specify the number format as 21 places? I formatted the whole
range with 0.0000E+00 and still got a zero sum. Thank you.

Kevin

 

[Jim Cone]

The formatting should be done before entering the numbers.
Go to Format (menu) | Cells | Number (tab)
Choose number in the "category" box.
Change the value in the "decimal places" box.

A quick, easy way to make sure numbers are treated as numbers (not text) is to...
Enter 1 in a blank cell.
Copy the cell.
Select your numbers.
Go to Edit | Paste Special
Checkmark "multiply" and click OK.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"Kevin Burton"
<[email protected]>
wrote in message
How did you specify the number format as 21 places?
I formatted the whole range with 0.0000E+00 and still got a zero sum.
Thank you.
Kevin

 

[Ron Rosenfeld]

For simplicty say I have two cells with values 0.0000004499190595611849428288
and 0.0000004499190595611849428288. If I have a formula that adds the two
cells (=A1 + A2) the value seems to be accurate. But if I use the 'SUM'
function (=SUM(A1:A2)) I get a result of zero, exactly. I have tried
formatting the result of the SUM with scientific notation just to see if the
value is just too small but it is really zero. I should see the sum to be
something like .0000009... but it is zero. This came up when I tried to SUM
about 31,000 data values (all small like this) and the result came up to be
zero. When I try to compute standard deviation or average I get a divide by
zero error, presumably because the SUM is zero. Any suggestions?

Thank you.

Kevin

If your cells are showing the values you have posted, then the values must have
been entered as text and not as numbers, as Excel is limited to 15 digit
precision, in accordance with the IEEE standards to which it, and many other
spreadsheet programs, adhere.

The SUM function ignores text in cells, so gives you a result of '0'

However, when you use the addition operator, Excel first tries to convert the
text strings to a number, which, in this case, is a value of
0.000000449919059561184 or 4.49919059561184E-07.

Of interest is that SUM (at least in Excel 2007) does not ignore TEXT if it is
entered directly as an argument. So the formula:

=SUM("0.0000004499190595611849428288","0.0000004499190595611849428288")

gives a result of approximately 9E-07.
--ron