 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 task, however it does not work on the second Excel spreadsheet. I have checked to see if the original sheet has a  reference page but it doesn't. So I am confused as to why this formula works on one Excel spreadsheet and not the other. Can you help me please.

16 Replies

# Re: Excel - Risk Rating calculation using "If(or)" function The one that says #REF

I don't see Matrix_Rating defined there too

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

Yes your right. It must refer to that "Risk_Matrix" in another document.

So I guess I'll have to do it using an inbuilt table in this document.

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

You can define that range in the Risk_Matrix Sheet as a named range "Matrix_Rating and your formula should work as is.

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

I'm still learning how to use advanced features in Excel, If I send this file to you, could you fix this for me please.

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

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.

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

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

Okay Thanks.

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

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.

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

Hello Yea,

Thanks for that doing this for me.

When I put the formula into the first Risk rating cell "Y15" and press enter the following error message comes up. Can you send back the document that you have corrected please.

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

I did it was attached to my last response, look at the bottom of my previous response.

best response confirmed by Jblake998 (Occasional Contributor)
Solution

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

This is the workbook that i sent on my response, and it works. Isn't it the right value? # Re: Excel - Risk Rating calculation using "If(or)" function

hello Yea,

Sorry I didn't see the attachment. it is very small.
I am happy to pay you for your time.
How much do I owe you please.

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

1,000,000.00 lol, just mark the post as the best answer.  Thank you

Cheers

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

Thank you so much.