Office & Productivity Software forum

General discussion

Excel displays zeros as negative

by random76 / November 30, 2004 2:59 AM PST

I have a spreadsheet where one column displays the result of a formula. For some reason, partway down this colum, Excel is displaying the zeros as -0.00 instead of just 0.00. I cannot work out why, or how to change this. Zeros display as 0.00 towards the top of the column. I am running Excel 97 on Windows XP. Does anyone have any idea as to how I can sort this out? It is messing up my conditional formatting as Excel considers -0 to be less than 0, and therefore ignores my formatting.

Discussion is locked
You are posting a reply to: Excel displays zeros as negative
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: Excel displays zeros as negative
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
Re: Excel displays zeros as negative
by Kees Bakker / November 30, 2004 5:05 AM PST


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.


Collapse -
Re: Excel displays zeros as negative
by random76 / November 30, 2004 6:05 PM PST

The formula I'm using is: SUM(G1172-D1173+E1173) and all the values are currency values - i.e. the values in that particular formula are -13.69, 0 and 13.69 (in that order) which should come out as 0! Although if I do put about 20 decimal places in the display I get a very small decimal, but I can't see why, given that all the way through I've been summing currency values. There shouldn't be anything smaller than 2 decimal places.

Collapse -
Re: Excel displays zeros as negative
by Kees Bakker / November 30, 2004 6:37 PM PST


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, 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.


Collapse -
Re: Excel displays zeros as negative
by MarkFlax Forum moderator / November 30, 2004 7:18 PM PST

That's still very strange though, (sorry, I'm butting in).

I did this on my Excel.

In cells A1:B1:C1 I entered, -13.69:0:13.69 and in cell D1 I used the AutoSum feature to sum these, and got 0. Not -0, but 0.

I tried all different variations of Formatting the 4 cells, eg as a number, as currency, with 2 decimal places, with more, as minus figures, etc, and always came up with 0 in D1.

This makes me think there must be something else.

Where do these figures -13.69, 0, 13.69 come from?

Are they calculated elsewhere and stored in these cells? If so, perhaps the calculation itself is producing an xth decimal place. eg something is creating the amount -13.690000000001 or whatever.

But even then, in my worksheet, cells formatted to currency with 2 decimal places does not create a -0.

Very strange.


Collapse -
Excel displays zeros as negative
by jttolliver / October 25, 2012 1:46 AM PDT

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

Popular Forums

Computer Newbies 10,686 discussions
Computer Help 54,365 discussions
Laptops 21,181 discussions
Networking & Wireless 16,313 discussions
Phones 17,137 discussions
Security 31,287 discussions
TVs & Home Theaters 22,101 discussions
Windows 7 8,164 discussions
Windows 10 2,657 discussions


Your favorite shows are back!

Don’t miss your dramas, sitcoms and reality shows. Find out when and where they’re airing!