Conditional Formatting a Formula Field

G

Guest

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")))))
 
T

T. Valko

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")))))
Click to expand...

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 Format>Conditional 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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula referring to another formula 2
Formula to another forumula? 10
Conditional Formatting based on a cell value 4
@Need help with a formula 8
Reference to another worksheet cell in a sumproduct formula 1
@Sum Conditional Values within a Macro 1
Conditional Format a cell that contains a function 1
Conditional Format over Date Range 3

Top