Strange formatting of Zeros

  • Thread starter Thread starter J Brian
  • Start date Start date
J

J Brian

I'm going crazy with this so I thought I'd ask if anyone
knows what's up. I've got identical columns with
numerical entries (Currency) and sum toals at the bottom
of the columns. when both are Zero, onw will show black
0.00 while the other will show up red -0.00. I know I
could change the number formatting to eliminate the red
but why qwould Zero show as a negative? Both columns are
setup the same. Just for curiosity, I tried changing the
range of the cell with the red result to only include the
last few cells of the column and it showed up as blac
without the minus just like the other one. But when I
have it calculate the entire column it shows up as red
with a minus sign.

Anyone have any idea why?
 
Because you have a negative value but since you probably are using currency
format with 2 decimal values it will show as $0.00 while it might be -0.001.
You can either round these numbers or use more decimal places or use
precision as displayed under tools>option>calculation.
 
Format the sums to General. You will see that the negative one is not
zero, though the difference from zero is smaller than 0.01.

I will guess that the negative column has values computed from formulas,
which do not compute exactly to the penny. I will further guess that
the "identical" positive column was hand entetered, using the rounded
values that are displayed in the negative column. As a result, they are
not really identical, even though they agree to two decimal places.
Make a third column that computes the difference, and format it to
General, then you will find where the differences are coming in.

If you don't want anything beyond the second decimal place, you either
have to explicitly round the formula results using the ROUND() function,
or else set Tools|Options|Calculate|Precision as displayed.

Jerry
 
Back
Top