5x5 risk calculation

Hello there


Can anyone please tell me what I am doing wrong with this formula? it is a risk calculation. I think i am making an error with the between numbers if than makes sense. As in what but less than 7 for example.

Your help would be greatly appreciated.

=IF([Inherent Risk Factor]@row = ""; ""; IF([Inherent Risk Factor]@row = <3; "VERY LOW"; IF([Inherent Risk Factor]@row < 7; "LOW"; IF([Inherent Risk Factor]@row < 11; "MEDIUM"; IF([Inherent Risk Factor]@row <= 17; "HIGH"; IF([Inherent Risk Factor]@row < 24; "VERY HIGH"; IF([Inherent Risk Factor]@row = 25; "CATASTROPHIC")))))))

Answers

  • Melly,

    It think it may be as simple as replacing the semicolons ( ; ) within the formula with commas.


    =IF([Inherent Risk Factor]@row = "", "", IF([Inherent Risk Factor]@row = <3, "VERY LOW", IF([Inherent Risk Factor]@row < 7, "LOW", IF([Inherent Risk Factor]@row < 11, "MEDIUM", IF([Inherent Risk Factor]@row <= 17, "HIGH", IF([Inherent Risk Factor]@row < 24, "VERY HIGH", IF([Inherent Risk Factor]@row = 25, "CATASTROPHIC")))))))

    Hope this helps!

    Heath Hilton

  • Leibel S
    Leibel S ✭✭✭✭✭✭

    @Melly

    The error is in the first line IF([Inherent Risk Factor]@row = <3

    You would need to change it to either <3Or <=3

    Please note that in the current formula format anything above 25 will not return anything and 24 will not return anything.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!