 SOLVED

# Help with excel formula

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. 11 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Help with excel formula

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)),"")`` # Re: Help with excel formula

Thank you so much for this!.. Much much appreciated.

# Re: Help with excel formula

How would you attached what you just sent me to an existing excel spreadsheet?

# Re: Help with excel formula

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.

# Re: Help with excel formula

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!  # Re: Help with excel formula

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: ``=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. # Re: Help with excel formula

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

# Re: Help with excel formula

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

Thank again.

# Re: Help with excel formula

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.

# Re: Help with excel formula

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.

# Re: Help with excel formula

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.