Forum Discussion
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)),"")
OliverScheurich 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!
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.
- I am most grateful for your assistance, thank you so much it is perfect now.
OliverScheurich 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.
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.