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)