Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet formulas | Excel Worksheet Functions | |||
Excel 2003 - Linking Formulas, Worksheet to Worksheet | Excel Discussion (Misc queries) | |||
Linking Formulas, Worksheet to Worksheet - Excel 2003 | Excel Discussion (Misc queries) | |||
Automatically pasting worksheet data to new worksheet with formulas | Excel Worksheet Functions | |||
copying formulas from worksheet to worksheet | Excel Discussion (Misc queries) |