Conditional format if value within a % of another cell value

nparsons75

Well-known Member
Joined
Sep 23, 2013
Messages
1,179
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...


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

Some videos you may like

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
Joined
Oct 24, 2012
Messages
4,577
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Oct 24, 2012
Messages
4,577
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Sep 23, 2013
Messages
1,179
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

Well-known Member
Joined
Oct 24, 2012
Messages
4,577
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

yes
 

nparsons75

Well-known Member
Joined
Sep 23, 2013
Messages
1,179
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


1613742854666.png
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,577
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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

Well-known Member
Joined
Sep 23, 2013
Messages
1,179
I have tried both ways as you describe in the last post, both options return this...

1613746185983.png
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,577
Office Version
  1. 365
Platform
  1. MacOS
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
 

Watch MrExcel Video

Forum statistics

Threads
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?

Disable AdBlock

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
Top