Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    IF AND

    ✭✭✭✭✭✭
    in Formulas and Functions

    Having trouble with a formula for RYG status. I need a statement for criteria: if its greater than 60% but less than 100%, turn the ball to Yellow.

    I have this: =IF(AND([% Complete]3 > 0.6, [% Complete]3 < 1, "Yellow"))

    But I keep getting either Incorrect Argument Set or Invalid Data Type.

    Why??!

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Answers

    • ✭✭✭✭✭

      Try this and see if it works. It looks like you're not closing the AND function before moving into the If True section of the IF function.

      =IF(AND([% Complete]@row > 0.6, [% Complete]@row < 1), "Yellow")

      image.png


      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭

      Thanks Malania! I got it to work as a single string but now can't get it to work in the full string: =IF([% Complete]@row < 0.6, "Red", IF(AND([% Complete]@row > 0.6, [% Complete]@row < 1), "Yellow"), IF([% Complete]@row = 1, “Green”))

      I always have issues with parens and need an easy way to remember how to get them right. I'm counting all of the open parens and match them with closing but it's still not working.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭

      I asked a colleague and it looks like we got it to work:

      =IF([% Complete]5 <= 0.6, "Red", IF([% Complete]5 < 1, "Yellow", IF([% Complete]5 = 1, "Green")))

      I think in the full string I don't need the AND statement since it's already indicated in the Red category?? This may have happened to me with an earlier formula as well. Thoughts?

      · Share on FacebookShare on Twitter
    • ✭✭

      Thank you very much for the prompt reply. It's almost working as expected. My formula is now as follows, =IF([Adjusted Score]2 < 11, "Tier 3", IF(OR([Adjusted Score]2 >= 12, [Adjusted Score]2 <= 20), "Tier 2", IF([Adjusted Score]2 > 21, "TIER 1")))

      The problem I am having is I have a project with an adjusted score of 27 but it's showing up as a tier 2 when I need it to be Tier 1. It's as if the formula is stopping at the 2nd operation, the If(Or portion and since this particular record is 12, it's coding it as tier 2. Any ideas?

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭

      @Ed Close I think you posted in the wrong discussion!

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions