Multiple reference Formula

NTG

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. 

 

Mike 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 Wilday

Hopefully, that will work for you! 

NTG

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

NTG

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 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", )))))

NTG

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",))))

 

 

Mike Wilday

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

NTG

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

Mike Wilday

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