excel floating digits error

[Bewilder]

I have created a chart with 2 columns of Nos. and a third column with the
results from the subtraction of column 1 from 2. At the bottom of each colum
I also have the resultant of the column. the display is to 0 digits but the
input on each box is to 2 digits. Also I have selected the numerical to show
in Red and parenthesis if it is a negative No. The final resultant at the
bottom of column 3 should equal 0. I cannot round to display because I would
losse the 2 digit accuracy on the input boxes.
The problem that I'm having is that the final resultant shows a negative
0. If I extend the final result to show max. digits to see why is a negative
0,
somewhere around the 20th. digit numbers starts to show up instead of 0.
Where do these numbers come from? I only inputed to 2 digits, everything else
should be 0.

 

[Bill Martin]

I have created a chart with 2 columns of Nos. and a third column with the
results from the subtraction of column 1 from 2. At the bottom of each colum
I also have the resultant of the column. the display is to 0 digits but the
input on each box is to 2 digits. Also I have selected the numerical to show
in Red and parenthesis if it is a negative No. The final resultant at the
bottom of column 3 should equal 0. I cannot round to display because I would
losse the 2 digit accuracy on the input boxes.
The problem that I'm having is that the final resultant shows a negative
0. If I extend the final result to show max. digits to see why is a negative
0,
somewhere around the 20th. digit numbers starts to show up instead of 0.
Where do these numbers come from? I only inputed to 2 digits, everything else
should be 0.

---------------------

It's normal system rounding. Excel (and pretty much all other general software)
uses IEEE standard arithmetic which only carries about 14 digits. Doing the
difference of two close numbers brings the issue to the fore.

One solution is to replace your:
[ ] = B1 - A1

With:
[ ] = if(abs(B1-A1) < 0.000000001, 0, B1-A1)

Good luck...

Bill

 

[JR]

Try this at the bottom of your column...

example...
If(sum(C1:C20)<0,0,sum(C1:C20))

If the sum of your column is less than zero, it will now display zero, not
negative zero.

 

[joeu2004]

Try this at the bottom of your column...
If(sum(C1:C20)<0,0,sum(C1:C20))

Better: MIN(0,SUM(C1:C20))