Unparseable Error

Michelle Phillips
Michelle Phillips ✭✭✭✭
edited 12/09/19 in Formulas and Functions

I am trying to create a formula that will return different values based on multiple factors, and the information on the sheet is linked in from another sheet

my sheet is attached and the formula I am trying to use is below. Any help is greatly appreciated

 

Here is my formula

=IF([% Complete]1 = 1, "complete",),if(and([% Complete]1<1,[ESD]1>Today()),"Not Started"," "),If(and([% Complete]1<1, [ESD]1<Today()),"delayed"," "),if([Internal Deployed]1=true,"Internally Deployed",[ECD]1),If([External Deployed]1=true, "Externally Deployed",[ECD]1)

Regards

Michelle

3-5-2019 4-55-34 PM.jpg

Comments

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    Hi Michelle

    Great effort at nested IF statements.  I would recommend trying the following:

    =IF([% Complete]1 = 1, "Complete",IF(AND([% Complete]1<1,[ESD]1>Today()),"Not Started",IF(AND([% Complete]1<1, [ESD]1<Today()),"Delayed",IF([Internal Deployed]1="True","Internally Deployed",IF([External Deployed]1="True", "Externally Deployed",[ECD]1)))))

    Having played with the formula above the logic doesn't seem logical to me - let me write the narrative for this function (you might say - Yes that is exactly what I want!)

    The Status is determined as either "Complete", "Not Started", "Delayed", "Internally Deployed", "Externally Deployed" or the date from [ECD]@row.  If the % complete is 100% then set the status to "Complete"; else if the % Complete is less than 100% AND the ECD date is in the future then set the status to "Not Started"; else if the % Complete is less than 100% AND the ECD date is in the past then set the status to "Delayed", else <<At this point the only record that would go on would be any record where % complete is less than 100% and ECD date is today>> look at the Internal Deployment column, if it is set to True then set the Status to "Internally Deployed" otherwise look at External Deployed column and if that is True then set the status to "Externally Deployed" otherwise << at this point the only records that would go on are any where where % complete is less than 100% and ECD date is today AND BOTH internally deployed and externally deployed are FALSE >> return the ECD date for the row.

    It will work, but is that what you wanted it to do?

    Good luck!

    Kind regards

    Debbie Sawyer Consultant & Training Manager

     

     

    Smarter Processes mean happy people in successful businesses

  • Debbie.

    Thank you.  Most of the story is spot on, there are just 2 items that should be interpreted differently.

    else <<At this point the only record that would go on would be any record where % complete is less than 100% and ECD date is today this needs to be if the expected date is today or in the future>> look at the Internal Deployment column, if it is set to True And the external deployment date is false then set the Status to "Internally Deployed" otherwise look at External Deployed column and if that is True then set the status to "Externally Deployed" otherwise << at this point the only records that would go on are any where where % complete is less than 100% and ECD date is today or in the future  AND BOTH internally deployed and externally deployed are FALSE >> return the ECD date for the row.

    Thank you

    Michelle

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    Hi Michelle,

    Great to hear from you and I hope the formula is parsing now!

    You mention that the "Story" is not quite right.  You mention that: <<At this point the only record that would go on would be any record where % complete is less than 100% and ECD date is todaythis needs to be if the expected date is today or in the future>>

    You already have a condition earlier in the formula that deals with this criteria, i.e. the ones that are less than 100% complete and the ECD is in the future is going to set the status to "Not Started", in meeting this condition you the formula will not continue to the section where you are then looking at internally deployed or not.  Do you need the Internally Deployed check to be earlier in the formula to take precedance over the Not Started condition?

    Can you give me the logic for this i.e. if % complete is less than 100% and the ECD date is in the future, then look at the Internal Deployment column, if it is set to True And the external deployment date is false then set the Status to "Internally Deployed" (I.e. look at all 4 conditions and set Internally Deployed if all 4 are true) otherwise if %complete is less an 100% and ECD date is in the future and the External Deployed column is True and Internally Deployed is False then set the status to "Externally Deployed" otherwise, then if it is % complete is less than 100% and ECD is in the future and both Internal and External deployement are FALSE, set to "Not Started" (and forget about returning the ECD@row in the status)

    Hang on in there! We nearly have this complicated nesting cracked!

    Kind regards

    Debbie

  • Debbie,

    The external deployment should never be true if the internal deployment is false.

    Normal process is

    Not starte> In Progress>Internal Deployment>external deployment> delayed

    Ultimately I want to get to the point I can create a visual close if not the same as as the slides attached

    thanks

    Michelle

     

    3-11-2019 11-38-31 AM.jpg

    3-11-2019 11-38-31 AM.jpg

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    Hi Michelle

    Would it be possible to set up a quick zoom meeting with you so we can get this formula working for you!?

    My email address is [email protected]

    I think it might be quicker to get to a solution this way!

    Kind regards

    Debbie

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!