Conditional Formatting - Variance of Percentage compared to another cell

[chelsycvg]

I am needing to set up conditional formatting rules of a column based on how it compares to another cell. I do not want to add another cell to calculate out percentages because there's already a ton of visible data in the spreadsheet.

What I am needing to setup. Anyone have guidance on how to do this? I know it must have to do with the sequence of the way the rules are applied but can't quite wrap my brain around it.

 

[sxhall]

Hi Chelsycvg,

To clarify you want cell B33 to change colour if it's value is...

1. <+/-10% - green
2. >+/-10% & <+/-20% - amber
3. < +/-20% - red

when compared to cell C33?

Can do conditional formatting without any additional cells but need to clarify the rules first.

Steven

 

[Peter_SSs]

I know it must have to do with the sequence of the way the rules are applied

That is correct. I don't know if you might have blank cells and if you do how you want those formatted if at all but see if this is any use.
After you have applied the rules as shown below my mini-sheet, if the formatting does not seem right, go back to Conditions Formatting -> Manage Rules and make sure they appear in this order

21 11 11.xlsm
	B	C
33	135	135
34	135	160
35		100
36	54	80
CF Variance

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B33:C36	Expression	=ABS((C33-B33)/C33)<=0.1	text	NO
B33:B36	Expression	=ABS((C33-B33)/C33)<=0.2	text	NO
B33:B36	Expression	=B33<>""	text	NO