why do zero dollar values sometimes show red?
 

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?

why do zero dollar values sometimes show red?
 

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

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Michael C." <Michael wrote in message ...
|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?

why do zero dollar values sometimes show red?
 

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

"Michael C." wrote:

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?

why do zero dollar values sometimes show red?
 

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." <Michael wrote in message
...
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?

why do zero dollar values sometimes show red?
 

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 he

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Michael C." <Michael wrote in message ...
|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?

why do zero dollar values sometimes show red?
 

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.

"Mike H" wrote:

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

"Michael C." wrote:

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?

why do zero dollar values sometimes show red?
 

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.

"Peo Sjoblom" wrote:

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." <Michael wrote in message
...
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?

why do zero dollar values sometimes show red?
 

Hi Michale,

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Michael C." wrote in message ...
| 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 he
|
| http://support.microsoft.com/kb/78113
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "Michael C." <Michael wrote in message
...
| |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?
|
|
|