Traffic Light - Conditional Formatting

[Matt_Chelmsford]

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.

 

[Akashwani]

Hi Matt,

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

Excel Workbook
	A	B	C	D	E	F	G	H
1	Data		Green	Orange	Red		X
2	100	3	TRUE	FALSE	FALSE		100
3	90	2	FALSE	TRUE	FALSE
4	108	2	FALSE	TRUE	FALSE
5	70	1	FALSE	FALSE	TRUE
6	112	1	FALSE	FALSE	TRUE
7	80	1	FALSE	FALSE	TRUE
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

 

[ukmikeb]

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