Forum Discussion

Jblake998's avatar
Jblake998
Copper Contributor
Jul 16, 2021
Solved

Excel - Risk Rating calculation using "If(or)" function

I have copied this formula - =@IF(OR($M10="",$L10=""),"",INDEX(Matrix_Rating,MATCH($M10,Matrix_Likelihood,0),VALUE(LEFT($L10)))) from one Excel spreadsheet to another to perform the same Risk Rating...
Formulas and Functions
  • Yea_So's avatar
    Yea_So
    Jul 18, 2021

    Jblake998 

    This is the workbook that i sent on my response, and it works. Isn't it the right value?

     

Yea_So's avatar
Yea_So
Bronze Contributor
Jul 17, 2021

Jblake998 

 

What's wrong with it, I will try to help you.

Jblake998's avatar
Jblake998
Copper Contributor
Jul 17, 2021
I don't know how to define the Table range in the formula for the Risk Rating cell to auto fill with the Category of Risk (H18 etc.) and the colour for the risk rating.
  • Yea_So's avatar
    Yea_So
    Bronze Contributor
    Jul 17, 2021

    Jblake998 

     

    I downloaded the file you shared, I will study it and I will ask you questions to verify if my comprehension to the item is correct before applying the appropriate formula

    • Jblake998's avatar
      Jblake998
      Copper Contributor
      Jul 17, 2021
      Okay Thanks.
      I appreciate your help.
      • Yea_So's avatar
        Yea_So
        Bronze Contributor
        Jul 17, 2021

        Jblake998 

         

        Here you go.  The formula is:

        =INDEX(Consequence,MATCH(LEFT('Electrical-Commission '!W15,1),LEFT('Risk Matrix'!$A$5:$A$10,1),0),MATCH(LEFT(X15,1),LEFT('Risk Matrix'!$A$5:$F$5,1),0))

         

        and it seems to bring the color along with the color in the matrix as well.

         

Resources