Conditional format if value within a % of another cell value

[nparsons75]

I have a table as below. I need the cell AVG ALL to be conditionally formatted to show the following...

If AVG ALL is less than TARGET by no more than 5% then fill colour AMBER.
If AVG ALL is less than TARGET by more than 5% then fill colour RED.
If AVG ALL is equal to or more than TARGET then fill colour GREEN.

The current result for AVG ALL would be RED. If the value was 86% for example, the colour would be AMBER.

Appreciate any help. Thanks in advance...

TARGET	AVG 20	AVG 21	AVG ALL
88%	79%	75%	79%

 

[etaf]

i have setup 3 rules in conditional formatting

Book2
	A	B	C	D
1	TARGET	AVG 20	AVG 21	AVG ALL
2	88%	79%	75%	100%
3	88%	79%	75%	79%
4	88%	79%	75%	87%
5	88%	79%	75%	79%
6	88%	79%	75%	88%
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D6	Expression	=((D2-A2)/D2)>=0	text	NO
D2:D6	Expression	=AND(((D2-A2)/D2)>-0.05,((D2-A2)/D2)<0)	text	NO
D2:D6	Expression	=((D2-A2)/D2)<0.05	text	NO

 

[etaf]

I have used a % calculation =((D2-A2)/D2)<0.05
BUT as its just comparing % , then i suppose it would be better just to check the difference
D2-A2

Book2
	A	B	C	D
1	TARGET	AVG 20	AVG 21	AVG ALL
2	88%	79%	75%	100%
3	88%	79%	75%	84%
4	88%	79%	75%	87%
5	88%	79%	75%	89%
6	88%	79%	75%	50%
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D6	Expression	=(D2-A2)>=0	text	NO
D2:D6	Expression	=AND((D2-A2)>-0.05,(D2-A2)<0)	text	NO
D2:D6	Expression	=(D2-A2)<0.05	text	NO

 

[nparsons75]

Hi, thank you for the reply, do I apply these in the conditional formatting rules using a formula to determine which cells to format?

 

[etaf]

yes

 

[nparsons75]

Oddly its not working in my spreadsheet... no doubt something I am doing wrong. I have updated the columns in your formula to suit my actual columns below. In this example, AD is the column I would like the conditional formatting. Each of the results in the table should be red apart from AD31 which should be amber.

Any value that is within 5% of its targeted value should be in AMBER unless its greater than the target which will then be GREEN. AD35 for example should be GREEN

 

[etaf]

What formulas did you use

AD30 - AA30
should give you the difference from average to
so in the first example
79-89
gives a result of -10
as the avg all is less than the target

and so the rules would be

=(AD30-AA30)>=0 Green
AND((AD30-AA30)>-0.05,(AD30-AA30)<0 Amber
=(AD30-AA30)<0.05 = RED


you could save the AND , if you put in ORDER and Stop if true - Put the RED before the AMBER

=(AD30-AA30)>=0 Green STOP IF TRUE
=(AD30-AA30)<0.05 = RED STOP IF TRUE
=(AD30-AA30)<0 Amber STOP IF TRUE

 

[nparsons75]

I have tried both ways as you describe in the last post, both options return this...

 

[etaf]

using XL2BB

OEE Data.xlsx
	Z	AA	AB	AC
3	TARGET	AVG 20	AVG 21	AVG ALL
4	89%	79%	75%	79%
5	99.5%	99.4%	99.4%	99.4%
6	85%	79%	69%	78%
7	75%	62%	51%	61%
8	89%	74%	67%	74%
9	99.5%	99.5%	100.0%	100%
10	85%	78%	76%	78%
11	75%	58%	51%	57%
12	89%	71%	70%	71%
13	99.5%	99.5%	99.5%	100%
14	85%	82%	72%	81%
15	75%	58%	50%	57%
16	89%	85%	78%	84%
17	99.5%	100.0%	100.0%	100%
18	85%	85%	86%	85%
19	75%	73%	67%	72%
20	89%	84%		84%
21	99.5%	99.8%		100%
22	85%	81%		81%
23	75%	68%		68%
24	89%	94%	97%	94%
25	99.5%	99.5%	99.6%	99%
26	85%	78%	79%	78%
27	75%	73%	76%	73%
28	89%	95%	97%	95%
29	99.5%	99.2%	100.0%	99%
30	85%	83%	83%	83%
31	75%	78%	80%	78%
Sheet1

Cell Formulas
Range	Formula
AA4:AA31	AA4	=AVERAGEIF(B4:M4,"<>0")
AB4:AB31	AB4	=IFERROR(AVERAGEIF(N4:Y4,"<>0"),"")
AC4:AC31	AC4	=AVERAGEIF(B4:Y4,"<>0")
Z7,Z31,Z27,Z23,Z19,Z15,Z11	Z7	=Z4*Z5*Z6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AC4:AC31	Expression	=AND((AC30-Z30)>-0.05,(AC30-Z30)<0)	text	YES
AC4:AC31	Expression	=(AC4-Z4)<0.05	text	YES
AC4:AC31	Expression	=(AC4-Z4)>=0	text	YES
AC3	Cell Value	=0	text	NO
AC3	Cell Value	=0	text	NO
AB3	Cell Value	=0	text	NO
AB3	Cell Value	=0	text	NO
N3:AA3	Cell Value	=0	text	NO
Z3:AA3	Cell Value	=0	text	NO

 

[nparsons75]

Getting closer I believe, however the green will not work.