SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2557515%22%20slang%3D%22en-US%22%3EExcel%20-%20Risk%20Rating%20calculation%20using%20%22If(or)%22%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2557515%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20copied%20this%20formula%20-%20%3D%40IF(OR(%24M10%3D%22%22%2C%24L10%3D%22%22)%2C%22%22%2CINDEX(Matrix_Rating%2CMATCH(%24M10%2CMatrix_Likelihood%2C0)%2CVALUE(LEFT(%24L10))))%3C%2FP%3E%3CP%3Efrom%20one%20Excel%20spreadsheet%20to%20another%20to%20perform%20the%20same%20Risk%20Rating%20task%2C%20however%20it%20does%20not%20work%20on%20the%20second%20Excel%20spreadsheet.%20I%20have%20checked%20to%20see%20if%20the%20original%20sheet%20has%20a%26nbsp%3B%20reference%20page%20but%20it%20doesn't.%20So%20I%20am%20confused%20as%20to%20why%20this%20formula%20works%20on%20one%20Excel%20spreadsheet%20and%20not%20the%20other.%20Can%20you%20help%20me%20please.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2557515%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2557853%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20Risk%20Rating%20calculation%20using%20%22If(or)%22%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2557853%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1105600%22%20target%3D%22_blank%22%3E%40Jblake998%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20your%20problem%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_0-1626474911158.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F296539i189371BB15AC4A01%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Yea_So_0-1626474911158.png%22%20alt%3D%22Yea_So_0-1626474911158.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20one%20that%20says%20%23REF%3C%2FP%3E%3CP%3EI%20don't%20see%20Matrix_Rating%20defined%20there%20too%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2559102%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20Risk%20Rating%20calculation%20using%20%22If(or)%22%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2559102%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047584%22%20target%3D%22_blank%22%3E%40Yea_So%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%20your%20right.%20It%20must%20refer%20to%20that%20%22Risk_Matrix%22%20in%20another%20document.%3C%2FP%3E%3CP%3ESo%20I%20guess%20I'll%20have%20to%20do%20it%20using%20an%20inbuilt%20table%20in%20this%20document.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2559106%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20Risk%20Rating%20calculation%20using%20%22If(or)%22%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2559106%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1105600%22%20target%3D%22_blank%22%3E%40Jblake998%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20define%20that%20range%20in%20the%20Risk_Matrix%20Sheet%20as%20a%20named%20range%20%22Matrix_Rating%20and%20your%20formula%20should%20work%20as%20is.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

@Jblake998 

 

Here's your problem:

Yea_So_0-1626474911158.png

The one that says #REF

I don't see Matrix_Rating defined there too

@Yea_So 

 

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.

 

Thanks for your help.

@Jblake998 

 

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

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.

@Jblake998 

 

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

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.

@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

Okay Thanks.
I appreciate your help.

@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.

 

@Yea_So 

 

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. 

@Jblake998 

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

@Jblake998 

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

Yea_So_0-1626580521069.png

 

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.

@Jblake998 

 

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

 

Cheers

Thank you so much.
I really appreciate your help.

@Jblake998 

 

You are very welcome John.

 

Cheers