IF(AND
I am building out a risk register and I'd like to use a formula to calculate the Risk Level, it will have to pull multiple combinations to get the formula though and I'm hitting a snag in my IF(AND formula.
Essentially it needs to breakdown info input in my Risk Probability + Risk Impact columns to calculate the Risk Level (for all 25 combinations)
Rare + Insignificant = Low Risk
Rare + Minor = Low Risk
Rare + Moderate = Low Risk
Moderate + Major = Major Risk
etc, etc...
Answers

Hi,
This is great, hope more people will do Risk Scoring.
Are you by chance using a number to represent each level? For example: Rare might be a 1 and Insignificant might be a 1 so that a total (1x1) would equal Low Risk, or 1 to 3 = Low since you might have a 1 and 3 with Rare/Mod, ec.. There are other ways but it makes some things easier using a value system assignment. It would cut down on your IF's. You could have a hidden col. just for the total, then a series of If's to assign a level. That should mean less IFs.
If that doesn't work for your situation, please share the Formula and the matrix of results.
Thank you,
Kevin
Happy to help if I can.
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)

You can Nest 25 IF statements, but I think using Index and Match is better. I made a video for you.
We are going to create another sheet to store the scores. Then we will use INDEX and MATCH to connect the two. I use the "AIO" nomenclature to indicate All In One, but you can use whatever you like.
Two sheets: Risk Probability + Risk Impact and Risk Probability + Risk Impact Reference
Risk Probability + Risk Impact
Fields:
Risk Probability
Risk Impact
Index
Narrative
Formulas:
Index: =IFERROR(INDEX({Score}, (MATCH([Risk Probability]@row + [Risk Impact]@row, {AIO_}, 0))), "")
=IFERROR(INDEX({Narrative}, (MATCH([Risk Probability]@row + [Risk Impact]@row, {AIO_}, 0))), "")
Risk Probability + Risk Impact Reference
Fields:
Risk Probability
Risk Impact
AIO
Score
Narrative
Formula:
AIO: =[Risk Probability]@row + [Risk Impact]@row
Help Article Resources
Categories
Check out the Formula Handbook template!