Jul 11 2022 04:31 AM
Jul 11 2022 04:31 AM
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.
Jul 11 2022 05:47 AMSolution
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.
Jul 12 2022 03:22 AM
@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!
Jul 12 2022 04:41 AM
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:
In the attached file in sheet "Tabelle2" i entered this formula in cell G42 and copied it down to cell G46.
Jul 21 2022 03:05 AM
@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.
Jul 22 2022 05:31 AM
Jul 22 2022 05:57 AM
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:
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.