my formated column is displaying one cell incorrectly

[jungbugg40]

I have a spreadsheet with 4 columns; the 4th columns is a formula going
across the 3 columns a+b-c = d with d preferably equally zero. When I format
column d almost all of the columns that equal zero display as a dash
(preference). However periodically one or two of the rows will display as
0.00 and I can't seem to get the formatting to change to the dash. I have
tried to reformat all columns all other cells and to no avail. Has anyone
else run into this issue and if so how have u solved it. The only way I can
solve it is to actually put a zero in the cell.

 

[Bernard Liengme]

If the numbers are what mathematician call "real numbers" - have deceimal
point as in 1.23 - then a simple sum like =a1+b1-c1 may give a value that
not quite zero even thought zero is expected. Not just an Excel problem but
a computer problem (round -off error) resulting from how the IEEE convention
( see one of the links below) converts decimal numbers (base 10) to binary
numbers (base 2).
Try =ROUND(A1+B1-C1,12) to round the result to 12 places of decimal and
avoid the n'round off' error problem


Chip's clear explanation
http://www.cpearson.com/excel/rounding.htm

Floating-point arithmetic may give inaccurate results in Excel
http://support.microsoft.com/kb/78113/en-us

(Complete) Tutorial to Understand IEEE Floating-Point Errors
http://support.microsoft.com/kb/42980

What Every Computer Scientist Should Know About Floating Point
http://docs.sun.com/source/806-3568/ncg_goldberg.html

Visual Basic and Arithmetic Precision
http://support.microsoft.com/defaul...port/kb/articles/Q279/7/55.ASP&NoWebContent=1

Others:
http://support.microsoft.com/kb/214118

http://docs.sun.com/source/806-3568/ncg_goldberg.html

best wishes

 

[Gord Dibben]

What you see is not always what you have.

Assuming column D is Accounting format.

If exactly 0 you will see the dash

If not exactly 0 then you will get 0.0 instead of the dash

Your numbers may not add up to exactly 0 due to unshown decimals.

i.e. 1.001 in A1 will display as 1.00 when 2 DP

-1.00 in B1 will show as -1.00 when 2 DP

Add A1 + B1 will not be zero


Gord Dibben MS Excel MVP