SOLVED

Help with excel formula

Occasional Contributor

Hello. I really need urgent assistance with a formula in excel please. I have been trying for days!

 

I created a risk metrics which should have a dropdown with the likelihood and dropdown for consequence and then the result should appear in the same colour low, med, high..

 

Can someone please assist me urgently.

 

Nikkie1977_0-1657539041466.png

 

11 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@Nikkie1977 

This is the formula in cell E11 in the attached example. The formula is copied down to cell E24 and the dropdowns are in range C11:D24. Conditional formatting is entered for range E11:E24.

=IFERROR(INDEX($C$3:$G$7,MATCH(C11,$B$3:$B$7,0),MATCH(D11,$C$2:$G$2,0)),"")

likelihood consequence.JPG

Thank you so much for this!.. Much much appreciated.
How would you attached what you just sent me to an existing excel spreadsheet?

@Nikkie1977 

Without seeing the existing spreadsheet i can't tell. Maybe you can attach a picture of your spreadsheet without sensitive data or you can upload your file without sensitive data.

@Quadruple_Pawn Sorry about that.

 

Below i have attached what the top of the excel document looks like as well as where the formulas should be. Column E should be the likelihood dropdown, F Consequence dropdown and G the Risk level result. Appreciate your help so much thank you!

 

Top of excel doc.pngMiddle of excel doc.png

@Nikkie1977 

From the picture i'm not sure which columns contain which data but for my suggestion i assumed that the columns and data are arranged as indicated here:

product liability.JPG

=IFERROR(INDEX($C$8:$G$12,MATCH(E42,$B$8:$B$12,0),MATCH(F42,$C$7:$G$7,0)),"")

In the attached file in sheet "Tabelle2" i entered this formula in cell G42 and copied it down to cell G46.

likelihood.JPG

I am most grateful for your assistance, thank you so much it is perfect now.

@Quadruple_Pawn Hello. I hope you are well. I am in need of some of your expert assistance again please. It is for the same excel document you have been assisting me with and ask if you can help me with the formula for 2 things:

1) I need to establish a "Total Risk Level" per category.  The Total Risk Level results could be one of the following: Low - 1, Med - 2, High - 3, Critical - 4.

Based on the attached spreadsheet example in Column G for the first category 2x Critical & 1 Medium my calculation is:

2x Critical (4) + 1x Medium (2) = 8 divided by 3 which is the total number of risk levels per category = 2.67 which is HIGH RISK - 3...

 

2) After the above is done, I then need to establish the "Total Contract Risk Level" which is sum of each Total Risk Level per Category.  This result should also reflect the Low - 1, Med - 2, High - 3, Critical - 4.

 

I hope that I have made sense?  Please let me know.

 

Thank again.

    

@Nikkie1977 

Hello and thank you. I hope you are well too. In the attached file i've entered formulas which return the expected results if i correctly understand what should be done.

Wow, I am so amazed by how good you are. How can i become so good?

Is there a way that the result can be in the same colour? ie: Medium bold & yellow, Low bold & Green?

Thank you again and again.

@Nikkie1977 

You are welcome. I've learned a lot from the solutions of the experts of this community. In the microsoft support page there are explanations of formulas for example if you are interested in the CHOOSE function:

 

CHOOSE function (microsoft.com)

 

In the attached file i've added conditional formatting for the results in the TOTAL RISK LEVEL PER CATEGORY cells and in the TOTAL CONTRACT RISK LEVEL cell.