Unparseable Error

Michelle Phillips

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

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

Michelle Phillips

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

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

Michelle Phillips

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

 

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 debbie.sawyer@smarterbusinessprocesses.com

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

Kind regards

Debbie