Formula and Color Change in Cell
Hello,
I am wondering if it was possible for the "Initial Risk Score" cell color to change when the "Probability" and "Severity" is selected?
I currently have the following formula in all cells "Initial Risk Score"
=IF(AND(Probability1 = "Improbable", Severity1 = "Negligible"), "1", IF(AND(Probability1 = "Improbable", Severity1 = "Minor"), "3", IF(AND(Probability1 = "Improbable", Severity1 = "Moderate"), "5", IF(AND(Probability1 = "Improbable", Severity1 = "Major"), "7", IF(AND(Probability1 = "Improbable", Severity1 = "Critical"), "16", IF(AND(Probability1 = "Remote", Severity1 = "Negligible"), "3", IF(AND(Probability1 = "Remote", Severity1 = "Minor"), "9", IF(AND(Probability1 = "Remote", Severity1 = "Moderate"), "16", IF(AND(Probability1 = "Remote", Severity1 = "Major"), "21", IF(AND(Probability1 = "Remote", Severity1 = "Critical"), "30", IF(AND(Probability1 = "Seldom", Severity1 = "Negligible"), "5", IF(AND(Probability1 = "Seldom", Severity1 = "Minor"), "15", IF(AND(Probability1 = "Seldom", Severity1 = "Moderate"), "25", IF(AND(Probability1 = "Seldom", Severity1 = "Major"), "35", IF(AND(Probability1 = "Seldom", Severity1 = "Critical"), "50", IF(AND(Probability1 = "Occasional", Severity1 = "Negligible"), "7", IF(AND(Probability1 = "Occasional", Severity1 = "Minor"), "21", IF(AND(Probability1 = "Occasional", Severity1 = "Moderate"), "35", IF(AND(Probability1 = "Occasional", Severity1 = "Major"), "49", IF(AND(Probability1 = "Occasional", Severity1 = "Critical"), "70", IF(AND(Probability1 = "Frequent", Severity1 = "Negligible"), "10", IF(AND(Probability1 = "Frequent", Severity1 = "Minor"), "29", IF(AND(Probability1 = "Frequent", Severity1 = "Moderate"), "50", IF(AND(Probability1 = "Frequent", Severity1 = "Major"), "70", IF(AND(Probability1 = "Frequent", Severity1 = "Critical"), "100")))))))))))))))))))))))))
Essentially, I am trying to build a Risk Matrix. Where -
When Initial Risk Score (output) is between 1-15 the cell is has the score and green background, 16-29 the cell has the score and yellow background, 30-50 the cell has the score and orange background and lastly, 70-100 the cell has the score and red background.
I have tried using conditional formatting on the cell but it will not work.
any help is highly appreciated. Thank you
Best Answer
-
Try removing the quotes from the numbers you are outputting. Quotes will output a text string which will not be read as a number.
Answers
-
Try removing the quotes from the numbers you are outputting. Quotes will output a text string which will not be read as a number.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!