If Formula

I'm trying to add and additional item to this IF Statement, when the total column is exceeding 150 and the Total Percent Used Column is Blank. This is my existing IF Statement

=IF([Total Percent Used]@row < 0.5, "", IF(AND([Total Percent Used]@row >= 0.5, [Total Percent Used]@row <= 0.75), "Approaching", IF([Total Percent Used]@row > 0.75, "Notify", IF([Total Percent Used]@row < 100, "Exceeds"))))

I'm trying to add the following: IF(AND([Total Percent Used]@row = 0, [Total]@row >150000, "Review")

The second formula is not working when added or returns a blank. What am I missing

Best Answer

  • TracyS
    TracyS ✭✭✭
    Answer ✓

    Thank you for your help, this was my final formula

    =IF(AND([Total Percent Used]@row < 0.01, Total@row > 150000), "Review", IF([Total Percent Used]@row < 0.01, "", IF(AND([Total Percent Used]@row > 0.02, [Total Percent Used]@row <= 0.5), "In Range", IF(AND([Total Percent Used]@row >= 0.5, [Total Percent Used]@row <= 0.75), "Approaching", IF(AND([Total Percent Used]@row >= 0.75, [Total Percent Used]@row < 1), "Notify", IF([Total Percent Used]@row >= 1, "Exceeds"))))))

Answers

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭

    Hi @TracyS — the percent used column seems to be empty in most cases. You are looking for the specific value of "0", which does not occur in the screenshot. Try this instead:

    =IF(AND([Total Percent Used]@row = "", [Total]@row >150000, "Review")

  • bisaacs
    bisaacs ✭✭✭✭✭

    Hey @TracyS,

    Since you have a formula already checking if the % is less than 50, it'll trigger that true statement before it'll trigger the one you're trying to add (assuming you're adding it at the end of the chain of IFs. I'd put the new IF statement as the first IF in the larger formula first.

    Secondly, it appears you're missing a closing parenthesis around the AND portion of your statement:

    IF(AND([Total Percent Used]@row = 0, [Total]@row >150000), "Review")

    So as a whole, the formula should work if it's entered like this:

    =IF(AND([Total Percent Used]@row = 0, [Total]@row >150000), "Review", IF([Total Percent Used]@row < 0.5, "", IF(AND([Total Percent Used]@row >= 0.5, [Total Percent Used]@row <= 0.75), "Approaching", IF([Total Percent Used]@row > 0.75, "Notify", IF([Total Percent Used]@row < 100, "Exceeds")))))

    Hope this helps!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭

    If you do sometimes see the value 0 and also want to include that criteria, then update it to:

    =IF(AND(OR([Total Percent Used]@row = 0, [Total Percent Used]@row = ""), [Total]@row >150000, "Review")

  • TracyS
    TracyS ✭✭✭

    Ok, so nothing returned for the review on the blue line. I added the formula you provided:

    =IF(AND([Total Percent Used]@row = 0, [Total]@row >150000), "Review", IF([Total Percent Used]@row < 0.5, "", IF(AND([Total Percent Used]@row >= 0.5, [Total Percent Used]@row <= 0.75), "Approaching", IF([Total Percent Used]@row > 0.75, "Notify", IF([Total Percent Used]@row < 100, "Exceeds")))))

  • TracyS
    TracyS ✭✭✭
    Answer ✓

    Thank you for your help, this was my final formula

    =IF(AND([Total Percent Used]@row < 0.01, Total@row > 150000), "Review", IF([Total Percent Used]@row < 0.01, "", IF(AND([Total Percent Used]@row > 0.02, [Total Percent Used]@row <= 0.5), "In Range", IF(AND([Total Percent Used]@row >= 0.5, [Total Percent Used]@row <= 0.75), "Approaching", IF(AND([Total Percent Used]@row >= 0.75, [Total Percent Used]@row < 1), "Notify", IF([Total Percent Used]@row >= 1, "Exceeds"))))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!