Conditional Formatting issue - alternative implementation?

0
votes

I am having a problem with malfunctioning conditional formatting in Excel 2010, whereby "traffic lights" occasionally have the wrong color. I have several KPIs, with the same "traffic light"-style conditional formatting applied across the cell range: most traffic lights are correct, but some aren't. Monkeying around with, for example, clearing the cell's format and either re-defining it, or copying the format from a different cell, typically helps, but sometimes it does not, and then I am stuck and have to clear the cell's conditional formatting and paste ("hardcode") the appropriate traffic-light image onto it. Can anyone suggest a "traffic lights" implementation that does not depend on Excel's conditional formatting?

1
excelconditional-formatting
I'm more interested in seeing a sample of the CF rule's aberrant behavior. I have encountered problems with the percentages (see one solution here), but that is often caused by asking the CF rule to do something that it was never intended to do. – user4039065
@pnuts - yes, absolutely – Dimitri Shvorob
In my experience, Excel's colour conditioning works well, but applying a conditional format from one place to another often gives different results than what I want. I'd suggest that you try a few different ways of doing that to find one that works for your case. e.g. editing the range of the conditional format compared with Copy and Paste Special > Merge Conditional Formatting. And take note of the relative and absolute addresses you use of cells in the conditional format specification. – MattClarke
@Jeeped, thank you, your recipe worked. – Dimitri Shvorob

1 Answers

1
votes

I was able to reproduce the apparent rogue behaviour you described and may have a solution for you.

                           CF traffic lights with percentage
                                Columns A correct; column C not-so-much

Column C has a three icon Conditional Format rule as you described using 100 and 75 as the Value and Percentage as the Type. Column A (which appears to work correctly) uses 1 and 0.75 as the Value and Number as the Type.

               CF traffic lights percentage setup
                   Raw numerical values instead of percentages in Value text box(es)

So it would appear that Excel 2007 does not handle converting percentages within Conditional Formatting as well as it should. You shouldn't have to change the display of the values within your worksheet. Just modify the conditional format rules to use the underlying values of your percentages and Number in place of Percentage as the Type.

Delving a little deeper into this showed that using 100% and 75% (as opposed to 100 and 75 sans % symbol) for the Value(s)s while maintaining Percentage as the Type will get Excel to change these on-the-fly to 1 and 0.75 when you leave the corresponding Value text box while retaining Percentage as the Type. This seems contradictory to me, but it does provide the correct green-yellow-red traffic light behaviour.