Office & productivity software forum: Excel displays zeros as negative

Random76,

Presumably the result of the formula isn't exactly zero, but something like -0.0001 which is less than zero. Normally this displays as 0.00, but I can imagine that with conditional formatting used this will display negative.

If such a cell is, say, d10, make another cell with the formula =d10 and the standard format with 9 decimals to see the exact value.
And, for advice, post the conditional formula you use and that you would want to display 0.00, together with the exact value, if you can't work it out yourself.

It's just a little big vague now, not only for you, but for us here also.

Kees

Random,

Currency values are floating point values, and floating point values by definition aren't exact but only approximate.

Make judicious use of Excels round-function to change anything 'practically' 0.00 to 'exactly' 0.00.

Or use the 'precision as displayed' feature of Excel. See http://www.5starsupport.com/info/excel2k.htm, the last tip but one ("A quick way to round numbers"). But I don't know if this works in your case, because the conditional format doesn't do a calculation in the litteral sense.

Hope this helps.

Kees

In Excel 2010 I was seeing some Number formatted zero value cells displayed as (0.00). I expanded to 15 decimal places and found all zeros. I then changed format from Number to Currency and the ( ) 's went away. Changed format back to Number and continued to display correctly as 0.00