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!

 

[GlynB]

no problem

so the result in C1 does not matter

compared to which value - B1 or A1 ?

this is the difference compared to B1
=ABS(B1-A1)/B1

assuming A1 = 100 and B1 = 95
=ABS(B1-A1)/A1 = 0.05
BUT

=ABS(B1-A1)/B1 = 0.0526315789473684000000000000000000

as thats 5 but based on b1 which is 95

which may be the same as, just looking at the difference and comparing to B1

=ABS(C1/B1)>0.05

Book4
	A	B	C	D	E	F	G
1	100	105	5.0000		TRUE		FALSE
Sheet1

Cell Formulas
Range	Formula
C1	C1	=B1-A1
E1	E1	=OR(C1>B1*1.05,C1<B1*0.95)
G1	G1	=ABS(C1/B1)>0.05

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	Expression	=ABS(C1/B1)>0.05	text	YES

Hi Etaf, Huge thanks, that last one works perfectly (=ABS(C1/B1)>0.05). Thank you for your patience.

 

[GlynB]

Hi Etaf, Huge thanks, that last one works perfectly (=ABS(C1/B1)>0.05). Thank you for your patience.

Hi Etaf, sorry to trouble you again. The solution you provided works perfectly. However I now need to apply the same solution to multiple rows (over 5000) Is there a quick and easy way to do this please? eg =ABS(C2/B2)>0.05 through to =ABS(C5000/B5000)>0.05

 

[etaf]

yes
same formula
but select the range C2:C5000

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
C2:C5000 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=ABS(C2/B2)>0.05

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

 

[GlynB]

yes
same formula
but select the range C2:C5000

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
C2:C5000 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=ABS(C2/B2)>0.05

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

Hi Etaf. Thanks for coming back to me. That solution didn't work. All cells in the column turn red regardless of value unfortunately.

 

[etaf]

Ok,
so you have fixed the formula to only work with row 2 - the $

=ABS($I$2/$H$2)>0.05

the $ fix the column and the row
so
=ABS($I2/$H2)>0.05

will allow the row to change
or
=ABS(I2/H2)>0.05
as you are only selecting 1 column I to highlight the column does not need to be fixed

 

[GlynB]

Hi Etaf,

This latest fix seems to work up to a point but I now have a new problem. Please see attached. If the value entered in column B is anything above zero, the conditional formatting behaves as expected and turns the cell red if the value is greater than +/- 5%. However, if the value entered into column B is zero, then despite the value in column C being greater than +/- 5% the cell is not filled.

Demo1.xlsx
	A	B	C
1	100	100	0
2	200	0	-200
3	300	400	100.00
4	400	450	50.00
5	500	525	25
6	600	660	60.00
7	700	1	-699.00
Sheet1

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

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

 

[etaf]

Sorry I’m away for a few days with limited access and only the phone
Hopefully other members may look in
Otherwise I will towards end of week weekend ish

 

[GlynB]

Hi Etaf. Many thanks for the response. I'm not in any great rush so if no-one else responds, I'll await your return.

 

[etaf]

zero 0 as a base is the difficulty - as you have issues with percents based on zero

so in excel you get a DIV error

So if the base is 0 then what do you want the number to be flagged if above or below
we can add an IF - if the base is zero, then we can use a number + or - what number
5% - is that +5 and -5 ?????

 

[GlynB]

Hi Etaf, Many thanks for coming back to me. If the base (column B) is 0 then I would need the adjacent cell in column C to flag but only if the value in column A is greater than 0. I may be asking for the impossible here but I would also need column C cells to flag if the value in column B is greater than +/-5% column A.

It's not the end of the world if this cannot be achieved as I can filter on column B and deal with the rows where the base in column B is 0, independently.