Cell colour change relative to value

[GlynB]

HI All. Newbie here and I have, probably what you guys would consider to be simple, a particular problem which I'm struggling to fix.
The first part is simple, even for me, where I would like the value in cell A1 to be subtracted from the value is cell B1 then the resulting value automatically entered into cell C1. Now this, for me, is where it gets complicated. I would then like the value in C1 to be compared to B1 and if the value in C1 differs by more than 5% of B1, cell C1 turns red. Thanks in advance!

 

[etaf]

yes it can be achieved , with an IF maybe with an OR() / AND() combination in conditional formatting

we can do the existing 5% with the formula that already works

BUT if B = 0 - than what value to flag

but only if the value in column A is greater than 0.

so does not matter if less than zero

use
=IF(B1=0, A1>0,ABS(C1/B1)>0.05)
as the conditional formatting formula

Book2
	A	B	C
1	100	100	0
2	200	0	-200
3	300	400	100
4	400	450	50
5	500	525	25
6	600	660	60
7	700	1	-699
Sheet1

Cell Formulas
Range	Formula
C1:C7	C1	=B1-A1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:C7	Expression	=IF(B1=0, A1>0,ABS(C1/B1)>0.05)	text	NO

 

[GlynB]

Hi Etaf, That works perfectly. Huge thanks for your help and patience with this.

 

[etaf]

you are welcome