Hi all,

I would appreciate a bit of help! I am looking to color-code a nested if statement based on the final values. My statement runs like this:

if(x<=0,"Below Min",if(and(x>0,x<=100%),"In Range",if(x>100%,"Above Max")))

In reality, my statement has several more nested conditions, so that standard stoplight conditional formatting will not work I think.

Does anybody know how to apply conditional formatting so that the color of the text in the cell changes depending upon whether the result value is "Below Min", "In Range", etc.?

MANY THANKS!

Hope I can be of help, but have you tried using formulas and then the format options??

Check out this video

Conditional formatting - highlight entire row based on a cell value
The below is from a thread call "Conditional Formatting Cells based on overlapping range": it could a good example of multiple criteria for conditional formatting.

Quote Originally Posted by BennyH View Post

Tried copying but still getting an error message for formula 1-amber? Maybe a picture describes it best, i want the cell formatting to come back with red if its> +/-10%, amber within 5% to 10% (over or under spent) & green if its within -5% to 5% of budget

:

Budget vs Actual Variance

<-10.1% -10% -5.1% -5% 0% 5% 5.1% 10% 10.1%>

Hi Benny, sorry about the late reply but actually had to do some work!! Shocking turn events.

I redid your project and got the results you needed!!

How I did it was with two formulas, but both using the same colors to achieve the results.

Five formulas were used, I will list them below, from smallest percentage to largest and then go through the steps I took

=$D2<-10.1% Color Red

=AND($D2<=-5%,$D2>=-10.1%) Color Yellow

=AND($D2>=-5%,$D2<5%) Color Green

=AND($D2>=5%,$D2<=10.1%) Color Yellow

=$D2>10.1% Color Red

Steps taken to achieve results.

1) Highlight cells A2 too D6

2) Click on Conditional Formatting, Manage Rules

3) Click on New Rule

4) Click on Use a Formula

5) Paste formulas in to box

6) Click on the format box and choose your color

7) Click OK twice and there you have it

Make sure you remove the second $ and you have the <=>signs in their correct place and that should do it!!

Hope that points you in the right direction!