SOLVED

# Sharepoint List - Multiple IF AND function in calculated column

Copper Contributor

# Sharepoint List - Multiple IF AND function in calculated column

Hi, I'm trying to create a column that will calculate a value based on a risk matrix; I have come up with the formula below, but it's not working. I've also tried a switch formula without success. I've played around with swapping commas out for semicolons and removing line breaks but this also did not work.

Can anyone assist with a solution please?

So the calculation is based on a restricted list of values in Column [Likelihood] and a restricted list of values in Column [Consequence].

Thank you!

=IF(AND([Likelihood]="Rare (1)",[Consequence]="Insignificant (1)"),"Low",
IF(AND([Likelihood]="Rare (1)",[Consequence]="Minor (1)"),"Low",
IF(AND([Likelihood]="Rare (1)",[Consequence]="Medium (3)"),"Low",
IF(AND([Likelihood]="Rare (1)",[Consequence]="Major (4)"),"Moderate",
IF(AND([Likelihood]="Rare (1)",[Consequence]="Critical (5)"),"High",
IF(AND([Likelihood]="Unlikely (2)",[Consequence]="Insignificant (1)"),"Low",
IF(AND([Likelihood]="Unlikely (2)",[Consequence]="Minor (1)"),"Low",
IF(AND([Likelihood]="Unlikely (2)",[Consequence]="Medium (3)"),"Moderate",
IF(AND([Likelihood]="Unlikely (2)",[Consequence]="Major (4)"),"Moderate",
IF(AND([Likelihood]="Unlikely (2)",[Consequence]="Critical (5)"),"High",
IF(AND([Likelihood]="Possible (3)",[Consequence]="Insignificant (1)"),"Low",
IF(AND([Likelihood]="Possible (3)",[Consequence]="Minor (1)"),"Moderate",
IF(AND([Likelihood]="Possible (3)",[Consequence]="Medium (3)"),"Moderate",
IF(AND([Likelihood]="Possible (3)",[Consequence]="Major (4)"),"High",
IF(AND([Likelihood]="Possible (3)",[Consequence]="Critical (5)"),"High",
IF(AND([Likelihood]="Likely (4)",[Consequence]="Insignificant (1)"),"Moderate",
IF(AND([Likelihood]="Likely (4)",[Consequence]="Minor (1)"),"Moderate",
IF(AND([Likelihood]="Likely (4)",[Consequence]="Medium (3)"),"High",
IF(AND([Likelihood]="Likely (4)",[Consequence]="Major (4)"),"High",
IF(AND([Likelihood]="Likely (4)",[Consequence]="Critical (5)"),"Extreme",
IF(AND([Likelihood]="Almost Certain (5)",[Consequence]="Insignificant (1)"),"Moderate",
IF(AND([Likelihood]="Almost Certain (5)",[Consequence]="Minor (1)"),"Moderate",
IF(AND([Likelihood]="Almost Certain (5)",[Consequence]="Medium (3)"),"High",
IF(AND([Likelihood]="Almost Certain (5)",[Consequence]="Major (4)"),"Extreme",
IF(AND([Likelihood]="Almost Certain (5)",[Consequence]="Critical (5)"),"Extreme",""))))))))))))))))))))))))

2 Replies
best response confirmed by TPalmer1695 (Copper Contributor)
Solution

# Re: Sharepoint List - Multiple IF AND function in calculated column

@TPalmer1695 You are using more than 19 nested IF functions in calculated column formula which is not supported in SharePoint Online.

You will have to break your formula with batches of 19 IF functions and combine them using &. Check these links for more information and examples:

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

# Re: Sharepoint List - Multiple IF AND function in calculated column

@ganeshsanap Thank you for your assistance. You were correct and breaking the formula with "&" did the trick.

For anyone looking for the corrected formula for similar use, I have included this below.

=IF(AND([Likelihood]="Rare (1)",[Consequence]="Insignificant (1)"),"Low",
IF(AND([Likelihood]="Rare (1)",[Consequence]="Minor (1)"),"Low",
IF(AND([Likelihood]="Rare (1)",[Consequence]="Medium (3)"),"Low",
IF(AND([Likelihood]="Rare (1)",[Consequence]="Major (4)"),"Moderate",
IF(AND([Likelihood]="Rare (1)",[Consequence]="Critical (5)"),"High",""))))) &
IF(AND([Likelihood]="Unlikely (2)",[Consequence]="Insignificant (1)"),"Low",
IF(AND([Likelihood]="Unlikely (2)",[Consequence]="Minor (1)"),"Low",
IF(AND([Likelihood]="Unlikely (2)",[Consequence]="Medium (3)"),"Moderate",
IF(AND([Likelihood]="Unlikely (2)",[Consequence]="Major (4)"),"Moderate",
IF(AND([Likelihood]="Unlikely (2)",[Consequence]="Critical (5)"),"High",""))))) &
IF(AND([Likelihood]="Possible (3)",[Consequence]="Insignificant (1)"),"Low",
IF(AND([Likelihood]="Possible (3)",[Consequence]="Minor (1)"),"Moderate",
IF(AND([Likelihood]="Possible (3)",[Consequence]="Medium (3)"),"Moderate",
IF(AND([Likelihood]="Possible (3)",[Consequence]="Major (4)"),"High",
IF(AND([Likelihood]="Possible (3)",[Consequence]="Critical (5)"),"High",""))))) &
IF(AND([Likelihood]="Likely (4)",[Consequence]="Insignificant (1)"),"Moderate",
IF(AND([Likelihood]="Likely (4)",[Consequence]="Minor (1)"),"Moderate",
IF(AND([Likelihood]="Likely (4)",[Consequence]="Medium (3)"),"High",
IF(AND([Likelihood]="Likely (4)",[Consequence]="Major (4)"),"High",
IF(AND([Likelihood]="Likely (4)",[Consequence]="Critical (5)"),"Extreme",""))))) &
IF(AND([Likelihood]="Almost Certain (5)",[Consequence]="Insignificant (1)"),"Moderate",
IF(AND([Likelihood]="Almost Certain (5)",[Consequence]="Minor (1)"),"Moderate",
IF(AND([Likelihood]="Almost Certain (5)",[Consequence]="Medium (3)"),"High",
IF(AND([Likelihood]="Almost Certain (5)",[Consequence]="Major (4)"),"Extreme",
IF(AND([Likelihood]="Almost Certain (5)",[Consequence]="Critical (5)"),"Extreme","")))))

1 best response

Accepted Solutions
best response confirmed by TPalmer1695 (Copper Contributor)
Solution

# Re: Sharepoint List - Multiple IF AND function in calculated column

@TPalmer1695 You are using more than 19 nested IF functions in calculated column formula which is not supported in SharePoint Online.

You will have to break your formula with batches of 19 IF functions and combine them using &. Check these links for more information and examples:

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.