why do zero dollar values sometimes show red?

[Michael C.]

I am doing monetary calculations in an excel spreadsheet (great place to do
that, huh?).
Sometimes the calculated value is $0.00. Which is not a problem. The problem
is that sometimes the value is displayed in red.
I want negative values to be displayed in red, but not Zero values.
I'm doing standard addition/subtraction of nothing smaller than $.01
(nothing that is a fraction of a cent).
So if the value reads $0.00, then it really IS $0.00 and not $0.001 or lower.
How can I stop the incorrect display of the zero value in red while keeping
the negative values red?

 

[Niek Otten]

Increase the number of decimals to the maximum. You'll probably see that there *are* items with more decimals.
For an explanation, look here:

http://support.microsoft.com/kb/78113

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I am doing monetary calculations in an excel spreadsheet (great place to do
| that, huh?).
| Sometimes the calculated value is $0.00. Which is not a problem. The problem
| is that sometimes the value is displayed in red.
| I want negative values to be displayed in red, but not Zero values.
| I'm doing standard addition/subtraction of nothing smaller than $.01
| (nothing that is a fraction of a cent).
| So if the value reads $0.00, then it really IS $0.00 and not $0.001 or lower.
| How can I stop the incorrect display of the zero value in red while keeping
| the negative values red?

 

[Mike H]

Hi,

Try this. In A1. Set up a conditional format to show <0 values as red and
then format the cell to show 2 decimal places.

In a1 enter the formula
=0-0.0001
The cell will display 0.00 but the conditional formatting will turn it red
and I suspect this may be what you are experiencing.

If you format the cell again to show 4 decimal place you will see it is <0

Mike

 

[Peo Sjoblom]

Format the cell as general and expand the column width if necessary and you
will see that
you have a very small negative number. I assume you are using formula to
get these,
just wrap it in a ROUND function

=ROUND(SUM(A1:A10),2)


--


Regards,


Peo Sjoblom

 

[Michael C.]

That is odd... I looked at the entire column and the only place that this
occured was in fields I was calculating where the values were NOT whole
dollar amounts. Only this one that had cents not equal to zero. All the other
calculations did not experience this phenomena.

 

[Michael C.]

I did a combination of what you, Niek O and Peo S mentioned. A very annoying
quirk.
I looked at the entire column and the only place that this occured was in
fields I was calculating where the values were NOT whole dollar amounts. Only
this one that had cents not equal to zero. All the other calculations did not
experience this phenomena.

 

[Michael C.]

That is odd... I looked at the entire column and the only place that this
occured was in fields I was calculating where the values were NOT whole
dollar amounts. Only this one that had cents not equal to zero. All the other
calculations did not experience this quirk.

 

[Niek Otten]

Hi Michale,

Use the ROUND() function to get two decimals or use Tools>Options>Calculation>Precision as displayed. But do read HELP first to
know what the consequences are.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| That is odd... I looked at the entire column and the only place that this
| occured was in fields I was calculating where the values were NOT whole
| dollar amounts. Only this one that had cents not equal to zero. All the other
| calculations did not experience this phenomena.
|
| "Niek Otten" wrote:
|
| > Increase the number of decimals to the maximum. You'll probably see that there *are* items with more decimals.
| > For an explanation, look here:
| >
| > http://support.microsoft.com/kb/78113
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > |I am doing monetary calculations in an excel spreadsheet (great place to do
| > | that, huh?).
| > | Sometimes the calculated value is $0.00. Which is not a problem. The problem
| > | is that sometimes the value is displayed in red.
| > | I want negative values to be displayed in red, but not Zero values.
| > | I'm doing standard addition/subtraction of nothing smaller than $.01
| > | (nothing that is a fraction of a cent).
| > | So if the value reads $0.00, then it really IS $0.00 and not $0.001 or lower.
| > | How can I stop the incorrect display of the zero value in red while keeping
| > | the negative values red?
| >
| >
| >