Andy Pope has an example on his website at
http://www.andypope.info/charts/trafficlight.htm.
A simpler way might be just to use coloured cells. This is extracted from a
web page that I am building
Another useful technique that is used in management reports is to highlight
the data using the 'traffic lights' technique, on target items are in green,
items slightly slipping are shown in yellow, and big slippages are shown in
red.
The will be flagged as follows:
* green - estimated cost is less than or equal to budget
* yellow - estimated cost.is less than or equal to budget + 25%
* red - estimated cost is greater than budget + 25%
As well as showing the lights colours, the background is shaded to provide
emphasis.
The conditional formatting will be formula driven, and to avoid showing a
light on a blank line kit tests for no budget, as well as the spend status.
It would be very simple to do the highlighting by shading a cell, maybe the
status cell or one of the amount cells, such as the budget. Whilst this
works, it would be better to show as a traffic light shows it, a coloured
circle. This is achieved by using the Winding font.
To create this traffic lights, follow these steps:
* Step 1.
Select the first data row in the 'Status' column
Set the font of this cell to 'Winding' Enter a lower-case letter l in
the cell
Centre the text in this cell
Copy this cell down to the appropriate number of rows.
* Step 2.
Goto Menu Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of
=AND(B2<>"",D2<=B2)
Click the Format button
Select the Font Tab
Select bright green from the Color dropdown
Select the Pattern Tab
Select dark green from the palette
OK
* Step 3.
Goto Menu Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of
=AND(B2<>"",D2<=B2*1.25)
Click the Format button
Select the Font Tab
Select yellow from the Color dropdown
Select the Pattern Tab
Select dark green from the palette
OK
* Step 4.
Goto Menu Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of
=AND(B2<>"",D2>B2*1.25)
Click the Format button
Select the Font Tab
Select red from the Color dropdown
Select the Pattern Tab
Select dark green from the palette
OK
OK the dialog, and it is all done.
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
