Conditional Formatting with Conditions

[Setofskills]

I'd like to do a three color conditional format on one column IF another column has a certain value in that row.

For example, if the country is USA and the sales conversion target is 20%, I want to have a rule that makes a three-color spectrum based on the actual conversion rate compared to the target, like a normal three color conditional formatting rule can.

So for the table below, the actual percentage cell for store #2 would be red, the actual percentage cell for store #4 would be light green, and the actual percentage cell for store #5 would be a darker green. The other stores outside of the USA would remain unformatted.

Store #	Country	Target %	Actual %
1	UK	25%	35%
2	USA	20%	15%
3	Germany	30%	20%
4	USA	20%	21%
5	USA	20%	35%

<tbody>
</tbody>

I know how to have a three-color conditional formatting spectrum OR have a conditional on whether or not to format a cell, but I can't do both. That is, I can't have a cell have a cell be part of a variable color format AND also have a condition.

Any help would be greatly appreciated!

 

[powersp68232]

If you put the following into another cell you can base your conditional formatting off of it.

=IF(AND(B5="USA",D5>=0.3),1,IF(AND(B5="USA",D5<0.3,D5>=0.2),2,IF(AND(B5="USA",D5<0.2),3,0)))