How to Do Automatically Calculate a R/Y/G Heat Map?

K

Klaatu

I want to have a red/yellow/green color-filled cell in my worksheet
that automatically shows the current status of reaching a set numeric
goal.

If performance to date:

- is equal or greater than 100% of goal, then cell is green with a
black, bold G
- is equal or greater than 90%, but less than 100% of goal, then cell
is yellow with a black, bold Y
- is is less than 90% of goal, then cell is red with a white, bold R

Need the text in the cell in case the worksheet is printed on a
non-color printer.

Can anyone give me advice on whether Excel can automatically do this?

If so, how?

Thanks.
 
J

JE McGimpsey

One way:

Assume Goal in column A, Actual in column B. Assume also that 90% should
be yellow - you didn't specify. Then

C1: =IF(B1/A1<0.9,"R",IF(B1>=A1,"G","Y"))

make C1 black font, bold with a green background.

Choose Format/Conditional Formatting:

CF1: Cell value is equal to = "R"
Format1: Red pattern, white font

CF2: Cell value is equal to = "Y"
Format1: Yellow pattern
 
Top