Conditional Formatting Based on 2 Different Cells

Art Miller

I have done this in Excel but can’t seem to get it to work in SS.   I need conditional formatting to represent the following:
 
EAC Margin < BL Margin
BL Margin ≤ EAC Margin ≥ (BL Margin + 5%)
(BL Margin +5%) < EAC Margin 
 
Tried creating a separate column with a formula using IF.    But can’t seem to get the formula to work.    I am thinking that once I can get the formula to give a specified value for each scenario.  I can then set the conditional formatting to each value.
 
There might be an easier way to do this than what I am trying.  Appreciate any input on how to accomplish my objective  with or without a formula. 

Brett Evans

I believe this is the formula you want in the conditional column:

=IF(EAC1 < BL1, "One", IF(AND(EAC1 >= BL1, EAC1 <= [BL+5%]1), "Two", "Three"))

 

And then you can conditionally format off of that using the conditional formatting in the leftmost pane.  See example below.

 

https://app.smartsheet.com/b/publish?EQBCT=34a5e4505a674959af55e81ee15faea2