IF(AND

Kelly W

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

Kevin Smith

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.

James Keuning

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

https://youtu.be/jtNDTpHNpg8