How to write a formula if then formula with multiple conditions

Options

The formula I am looking for is if a PO Amount is greater than 50,000 then return a circle but if it is greater than 100,000 return a square and if it is less than 50,000 return a triangle.

I have tried =IF([PO Amount]@row <50,000, "triangle", IF[PO Amount]@row >50,000, circle, IF[PO Amount]@row >100,000, "square")

this does not work

Thank you in advance!

Best Answer

  • bisaacs
    bisaacs ✭✭✭✭✭
    Answer ✓
    Options

    Hey @RPlaud,

    The order that you put your condition checks in matter. Since you have >50000 being checked before >100,000, they system is never going to move beyond the 2nd check (since any number above 100,000 is also going to be above 50,000). You'd want it to look like this I think:

    =IF([PO Amount]@row >100,000, "square", IF[PO Amount]@row >50,000, "circle", IF[PO Amount]@row <50,000, "triangle")

    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!

Answers

  • bisaacs
    bisaacs ✭✭✭✭✭
    Answer ✓
    Options

    Hey @RPlaud,

    The order that you put your condition checks in matter. Since you have >50000 being checked before >100,000, they system is never going to move beyond the 2nd check (since any number above 100,000 is also going to be above 50,000). You'd want it to look like this I think:

    =IF([PO Amount]@row >100,000, "square", IF[PO Amount]@row >50,000, "circle", IF[PO Amount]@row <50,000, "triangle")

    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!

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/30/24
    Options

    @RPlaud

    I am sorry it took me so long to reply to your similar post. You would first need to create a symbol column and pick from the list of symbols that most closely matches what your looking for. Then you would need to build the formula in that same column.

    There is not one that is a triangle, circle, square, There is how ever a Circle "Yes", Triangle "Hold", Octagon "No".

    There is also Circle "Easy", Square "Intermediate", Diamond "Advanced", Double Diamond "Expert". Which one would work best for you?

    =IF([PO Amount]@row <50,000, "Hold", IF[PO Amount]@row >50,000, "Yes", IF[PO Amount]@row >100,000, "No")))

    Or

    =IF([PO Amount]@row <50,000, "Intermediate", IF[PO Amount]@row >50,000, "Easy", IF[PO Amount]@row >100,000, "Advanced")))

    You could also get custom symbols, How ever it is very convoluted and formulas are quite a bit different using Index Match to pull them.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • nschildt
    Options

    Hi @RPlaud- give this one a shot. It will look for the Lesser than 50,000 first, then the greater than 100,000 second and anything remaining will be between the 50,000-100,000.

    If(less than 50,000 return Triangle, else (If greater than 100,000 return, square, else return circle.))

    Formula -
    =IF([PO Amount]@row < 50,000, "triangle", IF([PO Amount]@row > 100,000, "square", "circle"))

    Thank you,

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!