# Conditional format if value within a % of another cell value

#### nparsons75

##### Well-known Member
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%

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### etaf

##### Well-known Member
i have setup 3 rules in conditional formatting

Book2
ABCD
1TARGETAVG 20AVG 21AVG ALL
288%79%75%100%
388%79%75%79%
488%79%75%87%
588%79%75%79%
688%79%75%88%
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D2:D6Expression=((D2-A2)/D2)>=0textNO
D2:D6Expression=AND(((D2-A2)/D2)>-0.05,((D2-A2)/D2)<0)textNO
D2:D6Expression=((D2-A2)/D2)<0.05textNO

#### etaf

##### Well-known Member
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
ABCD
1TARGETAVG 20AVG 21AVG ALL
288%79%75%100%
388%79%75%84%
488%79%75%87%
588%79%75%89%
688%79%75%50%
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D2:D6Expression=(D2-A2)>=0textNO
D2:D6Expression=AND((D2-A2)>-0.05,(D2-A2)<0)textNO
D2:D6Expression=(D2-A2)<0.05textNO

#### nparsons75

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

yes

#### nparsons75

##### Well-known Member
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

##### Well-known Member

What formulas did you use

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

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

##### Well-known Member
I have tried both ways as you describe in the last post, both options return this...

#### etaf

##### Well-known Member
using XL2BB

Cell Formulas
RangeFormula
AA4:AA31AA4=AVERAGEIF(B4:M4,"<>0")
AB4:AB31AB4=IFERROR(AVERAGEIF(N4:Y4,"<>0"),"")
AC4:AC31AC4=AVERAGEIF(B4:Y4,"<>0")
Z7,Z31,Z27,Z23,Z19,Z15,Z11Z7=Z4*Z5*Z6
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AC4:AC31Expression=AND((AC30-Z30)>-0.05,(AC30-Z30)<0)textYES
AC4:AC31Expression=(AC4-Z4)<0.05textYES
AC4:AC31Expression=(AC4-Z4)>=0textYES
AC3Cell Value=0textNO
AC3Cell Value=0textNO
AB3Cell Value=0textNO
AB3Cell Value=0textNO
N3:AA3Cell Value=0textNO
Z3:AA3Cell Value=0textNO

#### nparsons75

##### Well-known Member
Getting closer I believe, however the green will not work.

Replies
10
Views
321
Replies
7
Views
68
Replies
5
Views
71
Replies
10
Views
159
Replies
1
Views
81

1,127,238
Messages
5,623,549
Members
415,980
Latest member
Lidget

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back