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

Find more posts tagged with

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.