Conditional Formatting Zero Values

[jms2553]

Using 2010, I'm trying to apply conditional values to a row of data, based on data I receive from an outside source.

The result of my formula is that I am left with a row of data that is anywhere between 1 to negative 1 and my conditional formatting is set to change all values that equal a zero to yellow, all values above a zero to green, and all negative values red.

My problem is that for ease of viewing, I only want to show one decimal place and really don't care if the actual number is .0001. It is close enough to zero to be considered zero (for this purpose) and should be colored yellow. Unfortunately, although all I'm showing is the one decimal place, it's showing either green or for the negative .0001, it's showing red. Is there a way around this?

 

[venkat1926]

suppose sample data is as this from A1 down

<table border="0" cellpadding="0" cellspacing="0" width="64"><col style="width: 48pt;" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="background: yellow none repeat scroll 0% 0%; height: 12.75pt; color: windowtext; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" align="right" height="17" width="64">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="background: yellow none repeat scroll 0% 0%; height: 12.75pt; color: windowtext; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" align="right" height="17">0.001</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="background: yellow none repeat scroll 0% 0%; height: 12.75pt; color: windowtext; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" align="right" height="17">0.002</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="background: yellow none repeat scroll 0% 0%; height: 12.75pt; color: windowtext; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" align="right" height="17">0.003</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="background: rgb(51, 153, 102) none repeat scroll 0% 0%; height: 12.75pt; color: windowtext; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" align="right" height="17">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="background: rgb(51, 153, 102) none repeat scroll 0% 0%; height: 12.75pt; color: windowtext; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" align="right" height="17">1.001</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="background: rgb(51, 153, 102) none repeat scroll 0% 0%; height: 12.75pt; color: windowtext; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" align="right" height="17">1.002</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="background: red none repeat scroll 0% 0%; height: 12.75pt; color: windowtext; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" align="right" height="17">-1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="background: red none repeat scroll 0% 0%; height: 12.75pt; color: windowtext; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" align="right" height="17">-1.001</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="background: red none repeat scroll 0% 0%; height: 12.75pt; color: windowtext; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" align="right" height="17">-1.001</td> </tr> </tbody></table>

select A1
conditional formatting

condition 1
cell values is between 0.01 and -0.01
and then color patteren
condikton 2
cellvalue is greater than 0.01
condition 3
cell value is less than -0.01

try this . adjust if necessary