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 Statement Issue

    ✭✭✭
    in Formulas and Functions

    Smartsheet Community:

    I am Trying Have My Rag Change to Not Started When The Following Conditions Are Met and Add it to My Existing Formula:

    IF(AND([Schedule Variance]@row = 0, [Progress Status]@row = "Not Started", "Not Started"

    I Keep Receiving a Green Instead of Not Started. Her is My Complete Formula Below:

    =IF([% Complete (Actual)]@row = 1, "Blue", IF([Schedule Variance]@row = 0, "Green", IF([Schedule Variance]@row > 0, "Green", IF(AND([Schedule Variance]@row = 0, [Progress Status]@row = "Not Started", "Not Started", IF([Schedule Variance]@row <= -7, "Red", "Yellow"))))))

    Thanks

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • ✭✭✭
      Answer ✓

      I Figured it Out. I Had to Change The Order to a Higher Condition. See Below:

      =IF([% Complete (Actual)]@row = 1, "Blue", IF(AND([Schedule Variance]@row = 0, [Progress Status]@row = "Not Started"), "Not Started", IF([Schedule Variance]@row >= 0, "Green", IF([Schedule Variance]@row <= -7, "Red", "Yellow"))))

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭✭✭
      edited 05/10/24 
      =
      
IF([% Complete (Actual)]@row = 1, "Blue",
      
IF([Schedule Variance]@row >= 0, "Green",

IF(AND([Schedule Variance]@row = 0,[Progress Status]@row = "Not Started"), "Not Started",

IF([Schedule Variance]@row <= -7, "Red", "Yellow")

      Can you please try this one.

      We can merge your Green condition using >=

      Added missing parethesis for the AND function.

      ...

      · Share on FacebookShare on Twitter
    • ✭✭✭
      https://community.smartsheet.com/discussion/comment/435491#Comment_435491

      I am Still Receiving a Green Symbol Instead of "Not Started". Also I Merged The Greater Than = To

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

      Hey @VirtixIT,

      It looks like your IF/AND statement, if it returns true, isn't a color option for the circle. Also, because you put the AND after the IF([Schedule Variance]@row = 0, "Green"), the formula is going to process that condition before the AND statement. Try this:

      =IF([% Complete (Actual)]@row = 1, "Blue", IF(AND([Schedule Variance]@row = 0, [Progress Status]@row = "Not Started"), "Yellow", IF([Schedule Variance]@row >= 0, "Green", IF([Schedule Variance]@row <= -7, "Red", "Yellow"))))

      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!

      · Share on FacebookShare on Twitter
    • ✭✭✭
      Answer ✓

      I Figured it Out. I Had to Change The Order to a Higher Condition. See Below:

      =IF([% Complete (Actual)]@row = 1, "Blue", IF(AND([Schedule Variance]@row = 0, [Progress Status]@row = "Not Started"), "Not Started", IF([Schedule Variance]@row >= 0, "Green", IF([Schedule Variance]@row <= -7, "Red", "Yellow"))))

      · 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