why do zero dollar values sometimes show red?


M

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?
 
Ad

Advertisements

N

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?
 
M

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
 
P

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
 
M

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

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

Advertisements

M

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

Advertisements

N

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?
| >
| >
| >
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top