Worksheet Formulas

[Guest]

Looking to create a formula that provides upper case letter and color for
each Status cell address based on delta results derived from requirements and
O/H. (No color required for N/A status). See example:

Assumptions:

R - Red = Not capable of performing required function (<65%)
G - Green = Meets or exceeds requirements (>80%)
A - Amber = Limited capability requiring work arounds (>66%<79%)
N/A = Does not apply

Requirements O/H Delta Status

100 65 -35 R
200 160 -40 G
300 300 0 G
400 265 -135 A
0 0 0
N/A

Workbook has numerous spreadsheets. And numerous requirements fall below
65%. Moreover, management concerned about all requirements, but "red" in
particular.

Thanks.

 

[JE McGimpsey]

Not sure about your comparisons - you say Red should be for < 65%, yet
you show =65% is also Red...

However, one way:

D2: =LOOKUP(B2/A2,{0,"R";0.6500000001,"A";0.8,"G"})

Select the Status cells (e.g., column D) and choose Format/Conditional
Formatting:

CF1: Cell value is equal to ="R"
Format1: <red>

CF2: Cell value is equal to ="A"
Format2: <amber>

CF3: Cell value is equal to ="G"
Format3: Green

 

[Guest]

Thanks for providing the answer to my formula question. The suggested
solution significantly reduced the amount of formula copying in the
worksheets. Again, much appreciated.
Manyfaces