Performance Measures and Conditional Formatting

G

Giacomo

I have a matrix and in the matrix I have indicators. Directly below
all the indicators is the same matrix on the same sheet but filled in
with monthly targets. I would like to link the conditional format to
the target so when the data is entered it will change to either green,
yellow, or red depending on whether it is 100%, 90-99.9%, or <90%
within the set target. Any help would be great. Thanks.


Ex.

Data

Indicator | Jan | Feb | March | .......
-------------------------------------------------------------
# of Tests | 10 | 9 | 8 |
-------------------------------------------------------------



Targets

Indicator | Jan | Feb | March | .......
 
G

gerdmain

Hi Giacomo,


Assume March data is in cell D2 and March Target is in cell D8 (change to
suit).

Conditional Formatting for Cell D2 should be like this
Cond 1 : Formula is ... =D8/D2<0.9 Pattern - red
Cond 2 : Formula is ... =D8/D2<1 Pattern - yellow
Cond 3 : Formula is ... =D8/D2=1 Pattern - green

Then use the format painter to copy the format into the other data cells.

Note: This shows as green if data and target are both empty. It shows red if
the target > 0 and data is empty. If you don't want this, the conditions
would need to be changed accordingly.

Have fun.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top