Conditional Formatting a Formula Field
 

Rather than creating a bunch of new fields, is it possible to conditional
format a field that has a formula in it? I've attached what I have below for
cell D5. We want anything 3 or greater to have the whole cell green and
anything below 3 to be red.

Thank you

=IF(C5=17.5%,"5",IF(C5=16.25%,"4",IF(C5=14.25%, "3",IF(C5=13%,"2",IF(C5=0,"1","N/A")))))

Conditional Formatting a Formula Field
 

One thing you need to do is "fix" your formula.

=IF(C5=17.5%,"5",IF(C5=16.25%,"4",IF(C5=14.25%, "3",IF(C5=13%,"2",IF(C5=0,"1","N/A")))))

When you enclose numbers in quotes like you have Excel treats them as TEXT
not numbers. So:

=IF(C5=17.5%,5,IF(C5=16.25%,4,IF(C5=14.25%,3,IF (C5=13%,2,IF(C5=0,1,"N/A")))))

Now, to apply the formatting:

Select cell D5
Goto the menu FormatConditional Formatting
Condition 1
Formula Is: =D5<3
Click the Format button
Select the Patterns tab
Select a fill color of RED
OK
Click the Add button
Condition 2
Formula Is: =AND(ISNUMBER(D5),D5=3)
Select the Patterns tab
Select a fill color of GREEN
OK out


--
Biff
Microsoft Excel MVP


"57Caddy" wrote in message
...
Rather than creating a bunch of new fields, is it possible to conditional
format a field that has a formula in it? I've attached what I have below
for
cell D5. We want anything 3 or greater to have the whole cell green and
anything below 3 to be red.

Thank you

=IF(C5=17.5%,"5",IF(C5=16.25%,"4",IF(C5=14.25%, "3",IF(C5=13%,"2",IF(C5=0,"1","N/A")))))

Conditional Formatting a Formula Field
 

Ah, I see. I just removed the quotes and it was fine. Thanks

"T. Valko" wrote:

One thing you need to do is "fix" your formula.

=IF(C5=17.5%,"5",IF(C5=16.25%,"4",IF(C5=14.25%, "3",IF(C5=13%,"2",IF(C5=0,"1","N/A")))))

When you enclose numbers in quotes like you have Excel treats them as TEXT
not numbers. So:

=IF(C5=17.5%,5,IF(C5=16.25%,4,IF(C5=14.25%,3,IF (C5=13%,2,IF(C5=0,1,"N/A")))))

Now, to apply the formatting:

Select cell D5
Goto the menu FormatConditional Formatting
Condition 1
Formula Is: =D5<3
Click the Format button
Select the Patterns tab
Select a fill color of RED
OK
Click the Add button
Condition 2
Formula Is: =AND(ISNUMBER(D5),D5=3)
Select the Patterns tab
Select a fill color of GREEN
OK out


--
Biff
Microsoft Excel MVP


"57Caddy" wrote in message
...
Rather than creating a bunch of new fields, is it possible to conditional
format a field that has a formula in it? I've attached what I have below
for
cell D5. We want anything 3 or greater to have the whole cell green and
anything below 3 to be red.

Thank you

=IF(C5=17.5%,"5",IF(C5=16.25%,"4",IF(C5=14.25%, "3",IF(C5=13%,"2",IF(C5=0,"1","N/A")))))

Conditional Formatting a Formula Field
 

You're welcome!

--
Biff
Microsoft Excel MVP


"57Caddy" wrote in message
...
Ah, I see. I just removed the quotes and it was fine. Thanks

"T. Valko" wrote:

One thing you need to do is "fix" your formula.

=IF(C5=17.5%,"5",IF(C5=16.25%,"4",IF(C5=14.25%, "3",IF(C5=13%,"2",IF(C5=0,"1","N/A")))))

When you enclose numbers in quotes like you have Excel treats them as
TEXT
not numbers. So:

=IF(C5=17.5%,5,IF(C5=16.25%,4,IF(C5=14.25%,3,IF (C5=13%,2,IF(C5=0,1,"N/A")))))

Now, to apply the formatting:

Select cell D5
Goto the menu FormatConditional Formatting
Condition 1
Formula Is: =D5<3
Click the Format button
Select the Patterns tab
Select a fill color of RED
OK
Click the Add button
Condition 2
Formula Is: =AND(ISNUMBER(D5),D5=3)
Select the Patterns tab
Select a fill color of GREEN
OK out


--
Biff
Microsoft Excel MVP


"57Caddy" wrote in message
...
Rather than creating a bunch of new fields, is it possible to
conditional
format a field that has a formula in it? I've attached what I have
below
for
cell D5. We want anything 3 or greater to have the whole cell green and
anything below 3 to be red.

Thank you

=IF(C5=17.5%,"5",IF(C5=16.25%,"4",IF(C5=14.25%, "3",IF(C5=13%,"2",IF(C5=0,"1","N/A")))))