# Traffic Light - Conditional Formatting

#### Matt_Chelmsford

##### Board Regular
Hello there,

I am struggling to put a set of traffic lights to 3 different criteria;

Green - If an Amount exactly the same X

Orange - If an Amount is with 10% of X

Red - If an Amount is not the same as X and differs by more than 10%.

Using 2010 and not sure it can be done with
any of the icon sets.

I would be grateful and appreciate any help given.

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Hi Matt,

If you are prepared to use a "Helper" column adjacent to your data, then perhaps something like this could work....

Excel Workbook
ABCDEFGH
1DataGreenOrangeRedX
21003TRUEFALSEFALSE100
3902FALSETRUEFALSE
41082FALSETRUEFALSE
5701FALSEFALSETRUE
61121FALSEFALSETRUE
7801FALSEFALSETRUE
8
Sheet4

My Excel maths is not very good, so the above formula in B2 may be incorrect, but it does appear to work.
I have used cell fill to illustrate the conditions being met, as Icons don't seem to copy over to the forum.
As you can see I have used cell G2 to house the value of X, you could put that value in your formula if you wished....
=IF(\$A2=100,3,IF(AND(100<>\$A2,ABS(((\$A2-100)/100)*100)<=10),2,1))

I hope this helps to point you in the right direction.

Good luck.

Ak

Hi

You can do it within Conditional Formatting without resorting to Helper cells.

After selecting the cells (in column A in this case) click Conditional Formatting>Manage Rules>Use a formula

enter =\$A2=100 (or replace 100 with the cell holding the value), format and fill the cell Green, before applying the formatting click the "Stop if True" box and Apply.

Complete the process with the remaining formulae.

hth

Replies
8
Views
389
Replies
1
Views
736
Replies
1
Views
1K
Replies
8
Views
670
Replies
0
Views
92

1,203,759
Messages
6,057,198
Members
444,913
Latest member
ILGSE

### 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.

### Which adblocker are you using?

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

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