Conditional Formatting against three cells and blanks

[gsmitchell]

Hello everyone! I have a situation where I will have a Target %, a Monthly Result % and possibly an Adjusted Target %. I need to color code the Monthly Result % based on the following:
1) If there is no Adjusted Target %, and the Monthly Result % is equal to or above the Target %, make it green.
2) If there is an Adjusted Target % and the Monthly Result % is equal to or above the Adjusted Target %, but not above the Target %, then make it blue.
3) If there is an Adjusted Target % and the Monthly Result % is equal to or above the Target %, then also make it blue.
4) If there is no Adjusted Target %, and the Monthly Result % is less than the Target %, make it red.

I can get the basics to work, but the part I am having trouble with is how to have the conditional formatting ignore the times when there is a blank Adjusted Target %. The problematic part is making the rules say "if there is no Adjusted Target %, then compare the Monthly Result % to the Target %".

Any thoughts on this would be appreciated.

 

[SanjayGMusafir]

Hello everyone! I have a situation where I will have a Target %, a Monthly Result % and possibly an Adjusted Target %. I need to color code the Monthly Result % based on the following:
1) If there is no Adjusted Target %, and the Monthly Result % is equal to or above the Target %, make it green.
2) If there is an Adjusted Target % and the Monthly Result % is equal to or above the Adjusted Target %, but not above the Target %, then make it blue.
3) If there is an Adjusted Target % and the Monthly Result % is equal to or above the Target %, then also make it blue.
4) If there is no Adjusted Target %, and the Monthly Result % is less than the Target %, make it red.

I can get the basics to work, but the part I am having trouble with is how to have the conditional formatting ignore the times when there is a blank Adjusted Target %. The problematic part is making the rules say "if there is no Adjusted Target %, then compare the Monthly Result % to the Target %".

Any thoughts on this would be appreciated.


View attachment 71183

Supposing Adjusted is column E then in conditional formatting formula use
$E3="" to tell blank $E3 cell
or
$E3<>"" to tell $E3 has some value

Also, when you want to tell check Cell E3 just use And function formula in Conditional formatting

Example for 1st condition

=AND($E3="",$C3>=$D3)