Formulas for an automatically generated Risk Level

Ruth
Ruth
edited 12/09/19 in Formulas and Functions

Hi

I'm am trying to replicate the below formulas in smartsheet so it can automatically calculate the Risk Level with existing controls and with additional mitigation. The below formulas are currently being used in excel:

Formula for existing controls Risk Level:

=IFERROR(VLOOKUP(H7&I7,LikeliConseq, 2, FALSE),"")

Formula for mitigation Risk Level:

=IF(AND(L7="",M7=""),J7,VLOOKUP(L7&M7,LikeliConseq, 2, FALSE))

I would like the end result to include the Risk Level in word format (e.g. Extreme, Medium, Low etc.) and the corresponding colour as per 'Risk Level Capture' attached.

Any advice?

Thanks

Risk Evaluation.PNG

Risk Level Capture.PNG

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 07/17/19

    You would need to set your table up  so that the Consequences are going across the top row above the table. Something like this (column names in bold):

    .

    TableA          TableB          TableC          TableD          TableE

                          Minor             Moderate      Major            Severe

    Certain

    Likely

    Possible

    Unlikely

    Rare

    .

    Of course you would fill in the rest of your table with the ratings.

    From there you would use something along the lines of this:

    =INDEX($TableB$2:$TableE$6, MATCH(Likelihood@row, $TableA$2:$TableA$6, 0), MATCH(Consequence@row, $TableB$1:$TableE$1, 0))

    .

    This leverages the MATCH function to give you a row number based on the likelihood and a column number based on the consequence to populate the appropriate fields in the INDEX function.

     

    EDIT:

    I forgot the color part...

     

    You would simply use conditional formatting to get the colors to pull through.

  • Thanks very much Paul!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!