And and If formual

Abitheblue

Hello,

I am trying to build a risk log in smartsheets that I currently have in excel, but I cannot get the formula to work.

I am trying to take the impact data and the probability date to give me a rating. This is what I am using at the moment but as you can see I am getting #unparseable.

=IFS(
AND([Impact]@row = "Insignificant", [Probability]@row = "Rare"), "Unlikely",
AND([Impact]@row = "Insignificant", [Probability]@row = "Unlikely"), "Possible",
AND([Impact]@row = "Insignificant", [Probability]@row = "Possible"), "Likely",
AND([Impact]@row = "Insignificant", [Probability]@row = "Likely"), "Almost Certain",
TRUE, "Rare" /* default or fallback */
)

Can anyone advise where I am going wrong or if this is not possible yet within smartsheets? or if there is a work around 😀

All help welcome as its driving me nuts.

Thanks

Abi

Kelly Moore

@Abitheblue

The syntax in the formula above is not what Smartsheet is expecting.

Try something like this

=IF(AND(Impact@row = "Insignificant", Probability@row = "Rare"), "Unlikely", IF(AND(Impact@row = "Insignificant", Probability@row = "Unlikely"), "Possible", IF(AND(Impact@row = "Insignificant", Probability@row = "Possible"), "Likely", IF(AND(Impact@row = "Insignificant", Probability@row = "Likely"), "Almost Certain", "Rare /* default or fallback */"))))

You will probably have to also incorporate the OR function as you capture all the possibilities for your Risk Rating. The syntax would be something like this:

=IF(AND(Impact@row = "Insignificant", Probability@row = "Rare"), "Unlikely", IF(OR(AND(Impact@row = "Insignificant", Probability@row = "Unlikely"),AND(Impact@row = "Minor", Probability@row = "Unlikely")), "Possible", IF(AND(Impact@row = "Insignificant", Probability@row = "Possible"), "Likely", IF(AND(Impact@row = "Insignificant", Probability@row = "Likely"), "Almost Certain", "Rare /* default or fallback */"))))

Does this work for you? You may be able to search the Community Posts for Risk Rating and find more examples

Kelly