Conditional Formatting Help?

[CzechCzar]

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!

 

[Cosmic Wizard]

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!

 

[iliace]

Note that the first two rules have the box "Stop If True" checked.

Sheet1

	A
1	98%
2	-71%
3	-47%
4	185%
5	93%
6	101%
7	-25%
8	0%
9	158%
10	-40%
11	-44%
12	85%
13	142%
14	144%
15	-42%
16	59%
17	170%
18	200%
19	81%
20	-41%
21	75%
22	180%

<tbody>
</tbody>

Conditional Formats

Cell Nr.: / Rule Format A1 1. / Formula is =A1<0 Abc A1 2. / Formula is =A1<=1 Abc A1 3. / Formula is =A1>1 Abc <tbody> </tbody>

<tbody>
</tbody>

 

[Grasor]

Simplest method I can recommend is setting up three conditional formatting rules to satisfy your needs.

1) If(x<0,TRUE,FALSE)
2) If(and(x<0,x<=1),TRUE,FALSE)
3) If(x>1,TRUE,FALSE)

Set each rule to the formatting you desire and apply to each cell. When copying formatting to multiple cells, make sure you go back into the formula for each rule and remove the cell lock "$" as appropriate. If you are using a formula to spit out a string, i.e. "Below Max" then subsitute the variable with the string.

1) If($A$1 = "Below Min",TRUE,FALSE)
2) If($A$1 = "In Range",TRUE,FALSE)
3) If($A$1 = "Above Max",TRUE,FALSE)

Again, set this up for the first cell then go back and remove the '$' then copy it and "Paste Special - Formatting" to the cells you want it to apply to.

HTH,
-G

 

[CzechCzar]

Many thanks all. This is tremendously helpful. I now know how to apply conditional formatting!