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 didnt notice the CELL values changed

Lets try again

you may want to change where the equal lies, I have used =5% for amber AND for 0% = Green - easy to change as needed

OEE Data.xlsx
	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
3	TARGET	AVG 20	AVG 21	AVG ALL	KPI
4	89%	79%	75%	79%	Performance	-10%	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.
5	99.5%	99.4%	99.4%	99.4%	Quality	0%
6	85%	79%	69%	78%	Availability	-7%
7	75%	62%	51%	61%	OEE	-15%
8	89%	74%	67%	74%	Performance	-15%
9	99.5%	99.5%	100.0%	100%	Quality	0%
10	85%	78%	76%	78%	Availability	-7%
11	75%	58%	51%	57%	OEE	-18%
12	89%	71%	70%	71%	Performance	-18%
13	99.5%	99.5%	99.5%	100%	Quality	0%
14	85%	82%	72%	81%	Availability	-4%
15	75%	58%	50%	57%	OEE	-18%
16	89%	85%	78%	84%	Performance	-5%
17	99.5%	100.0%	100.0%	100%	Quality	1%
18	85%	85%	86%	85%	Availability	0%
19	75%	73%	67%	72%	OEE	-3%
20	89%	84%		84%	Performance	-5%
21	99.5%	99.8%		100%	Quality	0%
22	85%	81%		81%	Availability	-4%
23	75%	68%		68%	OEE	-7%
24	89%	94%	97%	94%	Performance	5%
25	99.5%	99.5%	99.6%	99%	Quality	0%
26	85%	78%	79%	78%	Availability	-7%
27	75%	73%	76%	73%	OEE	-2%
28	89%	95%	97%	95%	Performance	6%
29	99.5%	99.2%	100.0%	99%	Quality	0%
30	85%	83%	83%	83%	Availability	-2%
31	75%	78%	80%	78%	OEE	3%
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
AE4:AE31	AE4	=AC4-Z4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AC4:AC31	Expression	=AND((AC4-Z4)>=-0.05,(AC4-Z4)<0)	text	YES
AC4:AC31	Expression	=(AC4-Z4)<-0.05	text	YES
AC4:AC31	Expression	=(AC4-Z4)>=0	text	YES
AD28:AD30	Cell Value	=0	text	NO
AD31	Cell Value	=0	text	NO
AD24:AD26	Cell Value	=0	text	NO
AD27	Cell Value	=0	text	NO
AD20:AD22	Cell Value	=0	text	NO
AD23	Cell Value	=0	text	NO
AD16:AD18	Cell Value	=0	text	NO
AD19	Cell Value	=0	text	NO
AD12:AD14	Cell Value	=0	text	NO
AD15	Cell Value	=0	text	NO
AD8:AD10	Cell Value	=0	text	NO
AD11	Cell Value	=0	text	NO
AD3	Cell Value	=0	text	NO
AD4:AD6	Cell Value	=0	text	NO
AD7	Cell Value	=0	text	NO
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]

Hi, I do not know what im doing wrong but must be something...

Amber now doesn't show and gives a white cell. I have tried set the format of the cell to text also but that just gives a lot of decimal places and no change to the colour?

 

[etaf]

Thats what I had
what are the 3 formulas

 

[nparsons75]

Sorry, which three formulas?

 

[etaf]

in the conditional formatting rules
like mine
BUT with your Actual cells

=AND((AC4-Z4)>=-0.05,(AC4-Z4)<0) = AMBER
=(AC4-Z4)<-0.05 = RED
=(AC4-Z4)>=0 +GREEN

 

[nparsons75]

=AND((AE284-AB28)>=-0.05,(AE284-AB28)<0) = AMBER
=(AE28-AB28)<-0.05 = RED
=(AE28-AB28)>=0 = GREEN

 

[etaf]

Different cells
=AND((AE284-AB28)>=-0.05,(AE284-AB28)<0) = AMBER
=(AE28-AB28)<-0.05 = RED
=(AE28-AB28)>=0 = GREEN

should they all be AE28 & AB28
NOT AE284 ?

so

=AND((AE28-AB28)>=-0.05,(AE28-AB28)<0) = AMBER
=(AE28-AB28)<-0.05 = RED
=(AE28-AB28)>=0 = GREEN

 

[nparsons75]

UNBELIEVABLE......

=AND((AE284-AB28)>=-0.05,(AE284-AB28)<0) = AMBER

When altering the cell values, I hadn't deleted the 4...... Great spot, around 3 hours trying to get to the bottom of that!!

 

[etaf]

no problem
happened to me too - different Column in the AMBER

 

[nparsons75]

couldn't believe it....... just didn't see it.