Conditional Formatting - Trafficlights with a twist - Excel 2007

[R Pelle]

Hi board members, hope you can shed some more light on conditional formatting than I have been able to find with Google or searching this forum.

I am tracking Licence contracted and Licences deployed and reconciling this data. If there’s a discrepancy actions need to be taken to either e.g. increase the contract or reduce the number of licences reduced. Column F in excel shows me a combo of a Red Amber Green traffic light and the %

Cond formatting rules are as follows.
1. IF Licences deployed > licences contracted the result is >100% => Red traffic light (e.g. we need to buy more licences or clean up dormant users)
2. If # Deployed < 90% of contracted then result is a % somewhere between 00-90 => Green traffic light
3. If # deployed is >= 90% and <100 of # of contracted licences Result is nn% => Amber traffic light

Now all of that is very easy to do in Excel.

G=Green, A=Amber, R=Red, B=Black resemble the traffic light symbols displayed in Col F.

Formula in Col E = Col C minus Col D
Formula in Col F = Col D/Col C + conditional formatting traffic lights

<CODE>
Col A---Col B-------------Col C------Col D------Col E-----Col F
Comp---Licence Type-----Cntractd---Deployed--Variance----%
IBM-----Floating Licence-------100--------92----------8---A 92%
HP------Floating Licence------1470-------737--------733--G 50%
SOU----Floating Licence----140000----162051-----22051---R 115%
XYZ----Fixed Licence-------------1---------1 ---------0---A 100%
etc...
</CODE>

The sting is in the last example/row.
When a row has a Fixed licence and we are using that licence the result will be 100%. There is no action to be taken as it is impossible to have more licences deployed than you have contracted for. According to the conditional formatting rules it is given an Amber traffic light. Ideally it should have a green traffic light or if you opt for the set of four colour traffic lights (Red, Amber, Green and Black) you could use Black.

When I looked at the Conditional Formatting options I came across the following...
Icon, Value, Type
Type has four options: number, percent, formula and percentile.

I was hoping to use the formula option to define the black light to be used for Fixed Licence rows. Something along the lines of =IF(Bx=”Fixed Licence”,True, False)
But Excel doesn’t like that at all.

I do have a workaround by putting the trafficlights in Column G and applying Cond Formatting rules in Col G.

<CODE>
Col A---Col B-------------Col C------Col D------Col E-----Col F----Col G
Comp---Licence Type-----Cntractd---Deployed--Variance----%----TrfLight
IBM-----Floating Licence-------100--------92----------8---92%------A
HP------Floating Licence------1470-------737--------733--50%------G
SOU----Floating Licence----140000----162051-----22051---115%-----R
XYZ----Fixed Licence-------------1---------1 ---------0---100%-----B
etc...
</CODE>

QUESTION 1.
I would really like to know if it is possible as Described above. Resulting in


<CODE>
Col A---Col B-------------Col C------Col D------Col E-----Col F
Comp---Licence Type-----Cntractd---Deployed--Variance----%
IBM-----Floating Licence-------100--------92----------8---A 92%
HP------Floating Licence------1470-------737--------733--G 50%
SOU----Floating Licence----140000----162051-----22051---R 115%
XYZ----Fixed Licence-------------1---------1 ---------0---B 100%
etc...
</CODE>


QUESTION 2.
Not necessarily i.c.w. above, how should one use/code conditional formatting when using the Type = Formula option. I cannot find an example.

Hmmmm, I was hoping that by using code tags it would not make a mess of the examples. perhaps the Jpegs below clarify this better.

Thanks for your time!

Question 1:


Question 2.

 

[Jerry Sullivan]

Hi and Welcome,

The icon set dialog allows you to create 3 or 4 “bins” and assign an icon to each range of numbers. Setting aside your twist, you could use one Conditional Formatting (CF) rule and create 3 bins instead of the several CF rules you first tried:
>100% (Red)
90% – 100% (Yellow)
< 90% (Green)

The Number Type shown gives you a little more flexibility because the Percent Type has to be between 0-100%

The formula option allows you to specify a variable number based on a formula (IE: =$A$2*0.3 )

Unfortunately, Excel doesn’t allow you to use relative references so that makes the Formula Type for Icon Sets less powerful than the general CF Rule by Formula.

One option for your problem is to use a formula in Column F that will put a text value such as “Fixed” or “ – “ if it is a Fixed License or else the ratio for Floating Licenses.

Since it is text, you won’t get any traffic light which will distinguish it from the Floating Licenses. In the image below, the formula in F2 (copied down through F5) is =IF(B2="Fixed Licence","Fixed",D2/C2)

The challenge with trying to get a Green light for the Fixed Licenses is that the rule is only looking at the single value in Column F to determine the correct color light. In some cases 100% means Floating License (Yellow) and other cases 100% means Fixed License (Green).

One work-around to make this look the way you describe would be to make the value in Column F -1 (negative 1). In the image above, the formula in F8 (copied down through F11) is =IF(B2="Fixed Licence","Fixed",D2/C2)

Since this is less than .9 it will display as a Green light. To change the display from -100%, You can then use regular cell formatting (not CF) to make a custom % format in which negative values don’t have a negative sign or ().

Hope this helps!

 

[R Pelle]

Hi Jerry, when I logged on this morning my thread had dropped all the way to page 11. My my this is an active forum. I've had a quick look at your reply but wanted to thank you now for responding so quickly! Now off to re-read your response and try it out for myself. I think I like your solution with the "fixed" text option.


Rob

 

[R Pelle]

I went with the "fixed" option. Thanks for tackling this issue from another viewpoint. Often you get stuck in one way of thinking and it needs someone else to view the issue with 'fresh eyes'.

Sorry for the double post - editing is apparently only allowed within 10 minutes. Different forum different rules.