Worksheet Formulas
 

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.

Worksheet Formulas
 

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



In article ,
Manyfaces wrote:

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.

Worksheet Formulas
 

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

"JE McGimpsey" wrote:

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



In article ,
Manyfaces wrote:

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.