Skip to main content

Nested IFand AND statements? (Update one column based on 2 other column's data)

L

Hi All, I’ve been searching and trying different formulas for a while, and wondered if someone could help me?

I’m creating a RAID log where it will capture all risks and score them, depending on the values people have selected for the severity of the risk and likelihood (I have set these two columns up as select fields, so users have options to pick from).

I’d like the risk score column to update to a number depending on what is entered into the severity of risk and the likelihood column (a risk matrix).

For example if someone were to pick “High” as the severity of risk and “Likely” as the Likelihood the risk score would automatically write 16.
If someone were to pick “Low” for the severity of risk and then “Never” as the likelihood it would automatically write 2.

There are 25 different combinations.

I’ve managed to get this to work so far:

IF(AND({Severity of risk} = “Low”, OR(Likelihood = “Never”)), “2”)

But that is just for one of 25 possible outcomes. Everything else I’ve tried errors. Any suggestions welcome :slightly_smiling_face:

    V
    Forum|alt.badge.img+16

    Hi @Louise_Howells
    When you have a large matrix like this, you may want to use SWITCH instead of IF to keep things readable.

    Take a look at this post on how to do that: Adding Formulas to come up with final score - #2 by Vivid-Squid

    So in your case the Severity field gets a score, and the Likelihood Field gets a score and then you perform a math function on the results.

    L
    Vivid-Squid wrote:

    Hi @Louise_Howells
    When you have a large matrix like this, you may want to use SWITCH instead of IF to keep things readable.

    Take a look at this post on how to do that: Adding Formulas to come up with final score - #2 by Vivid-Squid

    So in your case the Severity field gets a score, and the Likelihood Field gets a score and then you perform a math function on the results.


    Thank you! I’ll try SWITCH next week and see if that helps. Thank you for your response.

    Alexey_Gusev
    Forum|alt.badge.img+23
    Louise_Howells wrote:

    Thank you! I’ll try SWITCH next week and see if that helps. Thank you for your response.


    Hi,
    You can also use type conversion (TRUE=1,FALSE=0) like this
    2*({Severity of risk} = “Low”)*(Likelihood = “Never”)+…
    and 24 more lines
    but I believe your matrix built by a similar logic, so you can look and analyze - ‘weight’ of risk column and ‘weight’ of {Likehood} and create simple math expression to achieve your goal.

    Reply


    Most helpful members this week

    Terms & Conditions