help with a formula question

Hi,

I'm trying to use this formula to evaluate whether a value should appear Red, Green or Yellow by multiplying to other values in the sheet, but I keep getting an #unparsable error.

=IF(sum([Severity]1*[Probability]1) >"1.0", "Red", IF(AND(sum[Severity]1*[Probability]1) >".50", (sum[Severity]1*[Probability]1) <".75"), "Yellow", "Green"))

I'm not sure why - if someone else has run into this issue, please let me know how to resolve it.

thanks,

Michelle

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You have a misplaced parenthesis. Move the opening parenthesis from before the 3rd "sum" to immediately after it.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Knowing what you want it to do, try this one instead...

    =IF(Severity@row * Probability@row > .75, "Red", IF(Severity@row * Probability@row > .5, "Yellow", "Green"))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Happy to help. 👍️


    With nested IFs, the formula will stop on the first true value. Using that logic, if it moves past an IF then that particular IF must already be false. So by the time we get to the "Yellow" output, we already know that it must be less than .75 so there is no real need to repeat that as a criteria.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers