Nested Formulas

Alicia_Murad90366

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. 

 

  

Mike Wilday

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? 

Mike Wilday

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

Alicia_Murad90366

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!  

Alicia_Murad90366

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 1-99% 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

Mike Wilday

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

Mike Wilday

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 due-date 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"))))

 

Alicia_Murad90366

Thank you so much this helped me a bunch! Got it to work now.