Multiple reference Formula
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
-
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"))))
-
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.
- Project Status
- 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.
- Project Status
-
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", )))))
-
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",))))
-
Remove the comma from after "Yellow". That's what's throwing the error.
-
Thanks Mike, was able to get to work. Much obliged!
-
Yay! You're welcome. Glad I could help you out.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.6K Get Help
- 63 Global Discussions
- 46 Industry Talk
- 387 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 466 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!
