Conditional Formatting + Rounding

[Deb G.]

I am trying to put 2 conditional formats on a cell that contains a rounded number. The format is: if the number in the cell is greater than X +2%, then show green; if it's less than X-2%, show red. The problem seems to be with the rounded number. For instance, my conditionally formatted cell contains the number 7.77 but shows as 8. In this case "X" is 10. The result shouldn't be red but that is the way it is showing. How can I fix this?

Thanks!

 

[Andrew Poulsom]

In your conditional format formula use:

ROUND(cell,0)

where cell is the reference to the cell you wish to format.

 

[Deb G.]

Thanks Andrew. I'm not sure I understand where the ROUND fits in. Currently, the conditional statement is "if the cell value is greater that $B$8+2%..."and the cell this statement refers to is AB8. AB8 is the cell that also has a previously rounded number.

 

[Deb G.]

Thanks Andrew. I'm not sure I understand where the ROUND fits in. Currently, the conditional statement is "if the cell value is greater than $B$8+2%..." and the cell this statement refers to is AB8. AB8 is the cell that also has a previously rounded number.

 

[Andrew Poulsom]

Your cell has the value 7.77 formatted to zero decimal places to displaye 8. Formatting is not the same as rounding - the underlying value is still 7.77. So 10 minus 7.77 is 2.23, which is more than 2.

 

[Deb G.]

I'm stil not clear where the "ROUND" feature is entered in the conditional format. Does it somehow go in the range in both conditions ?? Or is it a third condition??
This message was edited by Deb G. on 2002-11-18 14:16

 

[Andrew Poulsom]

It goes in place of any reference to the cell that contains 7.77.

 

[IML]

You need to use the "formula is" option under conditional formatting rather than "Cell value is" option. A formula to type in could be
=OR(ROUND(H6,0)>$H$4+2,ROUND(H6,0)<$H$4-2)
for Cell H6 compared to the fixed H4.

A quicker fix would simply be to leave your formatting as you have it and check precision as displayed from tools, option, calculations.

Good luck