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.

Find more posts tagged with

Formulas

Gantt View

Project Management

Comments

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.

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

smartshhet screen shot help request.JPG

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…