Formula and Color Change in Cell

Clayton DSouza

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

Paul Newcome

Try removing the quotes from the numbers you are outputting. Quotes will output a text string which will not be read as a number.

Paul Newcome

Try removing the quotes from the numbers you are outputting. Quotes will output a text string which will not be read as a number.