Nested Formulas
Hello Smartsheet Community,
I have been attempting to create an automated formula in the status column.
I tried the following two options.
=IF(AND("Complete" , IF([% Complete]4=100% , "In Progress" , IF([% Complete]4<50% , "Not Started" , IF([% Complete]4 = 0%, "Completed", "error")))))
=IF(Finish4 < TODAY(), [% Complete]4 <1), "Not Started", IF([% Complete]4 =0, "In Progress", IF([% Complete]4 <25, "Complete", IF([% Complete]4 =100, "Completed", "Error")))
I am trying to get the Status column to automatically change when I plug in 100% , range of 1% to 99%, or 0% into the % complete column.
I have attached the 3 columns I and working with in the screen shot. Also please keep in mind that I have a formula in the health status column, which is as follows:
=IF(ISBLANK(Start@row), "", IF(AND(Finish@row < TODAY(), Status@row <> "complete"), "Red", IF(AND(Start@row <= TODAY(), Status@row = "not started"), "Yellow", "Green")))
Sometimes I get and error of circular dependency or unparsable.
Any assistance would be really helpful. Thank you.
Comments

Hi there, the important thing to keep in mind when working with percentages is that to run formulas on them, you have to use decimals. This means that 100% = 1 and 50% = .5 and likewise. With that in mind, try this formula.
=IF(Finish4 < TODAY(), ISBLANK([% Complete]4), "Not Started", IF([% Complete]4 = 0, "In Progress", IF([% Complete]4 < .25, "Complete", IF([% Complete]4 = 1, "Completed", "Error")))
I'm not exactly sure if this is what you are looking for. If the percentage Complete is blank, then it will say not started. If it's set to zero it will say in progress. If its less then 25% then it will say complete, and if its 100% it will say Completed. Os that what you are looking to do?

Sorry, I missed an important part there. Use this one.
=IF(AND(Finish4 < TODAY(), ISBLANK([% Complete]4)), "Not Started", IF([% Complete]4 = 0, "In Progress", IF([% Complete]4 < .25, "Complete", IF([% Complete]4 = 1, "Completed", "Error")))

So I got the formula in the sheet and thank you very much the logic you used makes sense. However I am looking for the formula to accomplish the following. IF 0% the status column should equal "Not Started", If 1% to 99% status should equal "In Progress", If 100% status should equal "Complete". Can the formula be adjusted to retrofit that logic?
Some issues I found were: even though some of the %Complete column are at 0% none of the Status column say "Not Started". Some even say "Error" when the status column is at 55%.
Secondly should the formula be based off the finish column or the real finish column? I have a variance formula in there so I can see the lag time on a given task. Not sure if that makes a difference with what we are trying to accomplish.
I have attached two new screen shots so you can see what I am referencing.
Thank you for your help in this Mike!

One additional comment I have a formula in the health column as well I thought if we solutioned the formula in the status column the health formula would work in tandem. however that is not the case.
=IF(ISBLANK(Start@row), "", IF(AND(Finish@row < TODAY(), Status@row <> "complete"), "Red", IF(AND(Start@row <= TODAY(), Status@row = "not started"), "Yellow", "Green")))
when the status column changed to complete the ball color did not change to the accurate color. (i.e. complete= green, in progress= yellow if the start date has passed , not started
Necessary logic:
if % complete is 0% status equal not started; and health is yellow if status is not started and the start date is passed
if %complete 199% status in progress, health would be green unless end date has passed then red
if % complete is 100 status equal complete, health color green.
sorry this is so complicated

Okay for the status column, try this...
=IF(AND(Finish4 < TODAY(), ISBLANK([% Complete]4)), "Not Started", IF([% Complete]4 = 0, "Not Started", IF([% Complete]4 < 1, "In Progress", IF([% Complete]4 = 1, "Completed", "Error")))

I think in your case you will want to base the status off of the Finish date because that is your due date. But that is logic you'll have to determine on your end. If your actual finish is your revised duedate than that might be the date to compare in these instances. To set your ball color, try this formula. Please try the following formula in your status row and let me know the results.... I reordered it a little.
=IF(status@row = "Complete", "Green", IF(ISBLANK(Start@row), "", IF(AND(Start@row <=Today(), Status@row = "Not Started"), "Yellow", IF(AND(Finish@row < Today(), Status@row <> "Complete"), "Red", "Green"))))

Thank you so much this helped me a bunch! Got it to work now.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!