Conditional Formatting

[seanbtrini]

I have created a table to track contact milestones, I turned off the
display of decimals to avoid long numbers. Because of the accuracy of
the calculations I couldn't use "calculate as displayed", so now
because the decimals are present my formatting for less than, equal to
or greater than, doesn't work. Is there any other way to get this to
work without messing up my calculations.

Sean

 

[Guest]

In the absence of details about your situation, what is wrong with basing the
conditional format on a formula instead of a cell value? Then you could
round the cell value to determine the correct formatting.

Jerry

 

[seanbtrini]

In the absence of details about your situation, what is wrong with basing the
conditional format on a formula instead of a cell value? Then you could
round the cell value to determine the correct formatting.

Jerry






- Show quoted text -

Lets say I have a milestone number calculated for someone to meet, the
number is 9.012, I am displaying it as 9 on the sheet. I have a
conditional format of "if cell A1 is less than cell A2 (9.012) then
RED"; then the second format is "if A1 is equal to or greater than A2
then GREEN". Well if my cell A1 is 9 it will stay red because it is
less than A2, 9.012. I haven't used a formula because I don't know
how. I am in the learning mode.

Sean

 

[Guest]

In your conditional format for A1, change the expression from =A2 to
=ROUND(A2,0)

Note that it is not necessary to specify two conditions for this case, since
the combination of conditions cover every eventuality other than an error
value (note that a character string will satisfy >=A2). Simply
unconditionally format the cell to always match the 2nd condition (unless
over-ridden by the conditional format), and you then only need the first
condition.

Jerry