Microsoft Office Tutorials and References
In Depth Information
Set Up the Traffic-Light Formulas
This figure illustrates the simple traffic
light display.
Cell B2 has been named Score. It
typically contains a formula that looks at
the data to be displayed and then scores
the results. A score of 1 returns green
around the chart; 2 returns yellow; and 3
returns red.
The Score formula can be anything you
want. Suppose, for example, that a cell
named Growth contains the percentage
that the most-recent quarter has grown
from the previous quarter.
The following formula would return 1 if
sales have grown by more than 2%, 3 if
sales have fallen by more than 2%, and
2 otherwise.
=IF(Growth>0.02, 1, IF(Growth<- 0.02,
3, 2))
For now, however, just enter a value of
1, 2, or 3 as your Score.
Define the range names Green, Yellow,
and Red as =\$B\$14, =\$B\$16, and
=\$B\$18 respectively, as shown in the
figure. Define the range name Alert to
contain this formula:
=CHOOSE( Score, Green, Yellow, Red)
Search JabSto ::

Custom Search