Multiple reference Formula

Multiple reference Formula

NTGNTG
edited 12/09/19 in Formulas and Functions

Greetings:

Trying to create a formula that tracks task duration displayed by "symbol" in conjunction with another condition of the task:... complete, or not started. 

I was able to get the symbols to show up properly based on calculated duration. However, the bottleneck is related to the formula not picking up a symbol based on Task status of ..."Complete" or "Not Started"

The Formula I have working right now is: 

=IF([Actual Duration]1 < [Planned Duration]1, "Green", IF([Actual Duration]1 > [Planned Duration]1, "Red", IF([Actual Duration]1 = [Planned Duration]1, "Yellow")))

What I'm trying to include is that if it looks at the Column of "STATE"  that if it also equals {Completed} to display "Green" and if it equals {Not Started} to display "Gray" all in one formula. 

Thank you for your consideration. 

 

Comments

  • Mike WildayMike Wilday ✭✭✭✭✭

    You can combine if statements by using an AND or an OR function, 

    Like this... 

    =IF(State1="Not Started", "Gray", IF(OR([Actual Duration]1 < [Planned Duration]1,State1 = "Completed"), "Green", IF([Actual Duration]1 > [Planned Duration]1, "Red", IF([Actual Duration]1 = [Planned Duration]1, "Yellow"))))

    https://help.smartsheet.com/function/or

    https://help.smartsheet.com/function/and

  • Mike WildayMike Wilday ✭✭✭✭✭

    Hopefully, that will work for you! 

  • Thank you, I will give that a try, appreciative for your help. 

  • Hi Mike, hope all is well. it seems I'm still in the same situation. However, the scope has slightly changed.

    1. Project Status
      1. Errors: Unparseable

    Trying to have the formula recognize that if the Column of [STATE] = not started, internal hold or customer hold to = Grey

    Formula partially works.

    =IF([Actual Duration]1 < [Planned Duration]1, "Green", IF([Actual Duration]1 > [Planned Duration]1, "Red", IF([Actual Duration]1 = [Planned Duration]1, "Yellow", IF([Planned Duration]1 < [Actual Duration]1, "Green", IF(State32 = "Not Started", "Gray")))))

    It will recognize the formula to produce RED, Green, Yellow. But will not produce the Gray option. I've tried multiple approaches to no avail.

    attached is a screen shot. any suggestions would be appreciated.

     

  • Mike WildayMike Wilday ✭✭✭✭✭

    Try moving the gray criterion to the first position. The IF formula will always fire in the order of the statements. IF one of your IF statements is always true, then it will always fire at that statement. Try putting the mostly true statement at the end and the least true statements at the beginning.... does that make sense? Your planned duration will always be less than actual duration in most cases - probably even when its not started... so moving that around should do the trick. 

    Try this one. 

    =IF(State32 = "Not Started", "Gray", IF([Actual Duration]1 < [Planned Duration]1, "Green", IF([Actual Duration]1 > [Planned Duration]1, "Red", IF([Actual Duration]1 = [Planned Duration]1, "Yellow", IF([Planned Duration]1 < [Actual Duration]1, "Green", )))))

  • NTGNTG
    edited 04/05/18

    Hi, Mike and thanks for your time and input.

    Took you advise and left out the comparison of Planned Duration1 > actual duration1

    I tried putting the state1 = "Not Started" in front of the argurment...and smartsheet still not accepting it. It still comes up as unparseable... definately a user errror, I just cant see it.

    Current formula as follows:

    =IF([State]1 = "Not Started","Gray", IF([Actual Duration]1 < [Planned Duration]1, "Green", IF([Actual Duration]1 > [Planned Duration]1, "Red", IF([Actual Duration]1 = [Planned Duration]1, "Yellow",))))

     

     

    smartshhet screen shot help request.JPG

  • Mike WildayMike Wilday ✭✭✭✭✭

    Remove the comma from after "Yellow".  That's what's throwing the error. 

  • Thanks Mike, was able to get to work. Much obliged!

  • Mike WildayMike Wilday ✭✭✭✭✭

    Yay! You're welcome. Glad I could help you out. 

     

Sign In or Register to comment.