| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? | | | |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formatting dollar values in text | Excel Worksheet Functions | |||
| one column of cells show ####. Values show when I open it. Help | Excel Discussion (Misc queries) | |||
| Show Top 3 Values | Excel Worksheet Functions | |||
| 3 values, show which ones are same | Excel Worksheet Functions | |||
| Show zero values | Excel Discussion (Misc queries) | |||
Hybrid Mode
