none
Iif/Switch Error RRS feed

  • Question

  • Hello, 

    Formula noob here.

    I am trying to re-write a "working" formula in order to (hopefully) fix some #ERRORS and inaccurate results:

    % Complete Start Variance Task Status (Non Graphical)
    100% 0 days Incomplete
    100% 0 days Incomplete
    100% 0 days Incomplete
    100% 0 days Incomplete
    0% 0 days #ERROR
    0% 0 days #ERROR
    0% 0 days #ERROR
    0% 0 days #ERROR
    100% 0 days Incomplete
    0% 2 days Behind Schedule
    0% 0 days #ERROR
    0% 0 days #ERROR
    0% 0 days #ERROR
    92% 0 days  

    (Sorry, unable to post images, because my account is not verified for some reason...)

    The formula that is producing these results is below. Apologies for any incorrect indentation and all the missing manners of good programming

    IIf([% Complete] < 100, Switch( [% Complete] = 100, "Complete", ([% Complete] < 100 And ([Start] - [Baseline Start]) > 0), "Behind Schedule"

    ), "Incomplete" )


    What I am trying to change it to is this:

    IIf([% Complete] = 100,
    	Switch
    		([% Complete] < 100 And ([Start] - [Baseline Start]) < 0), "Incomplete",
    		([% Complete] < 100 And ([Start] - [Baseline Start]) > 0), "Behind Schedule",
    		"Complete"
       )

    I am unable to save the latter formula in an Enterprise Field and receiving this generic error:

    • This formula contains errors. Correct the formula and try again.

    My question is whether I am on the right track to resolve the #ERRORS and inaccurate results with the second formula, and if so how can I fix it?

    Thanks,

    BM

    Thursday, March 2, 2017 3:45 AM

Answers

  • Hi,

    if you are using switch, you don't need an additional IIF. So for your second formula, the following should work (you missed some ")" and I removed the IIF:

    Switch(
      ([% Complete] < 100 And ([Start] - [Baseline Start]) < 0), "Incomplete",
      ([% Complete] < 100 And ([Start] - [Baseline Start]) > 0), "Behind Schedule",
      ([% Complete] = 100),"Complete"
       )

    Another suggestion, since you are calculating with Baseline Start is to ensure, that there is a Baseline set, to avoid errors:

    Switch(
     ([% Complete]=100),"Complete",
     (projdatevalue([Baseline Start])=4294967295),"No Baseline",
     ([% Complete]<100 And ([Start]-[Baseline Start])<0),"Incomplete",
     ([% Complete]<100 And ([Start]-[Baseline Start])>0),"Behind Schedule"
          )

    Does that help?
    Barbara


    To increase the value of this forum, please mark the replies that helped to solve your issue as answer. If you find answers to questions from other forum participants to be helpful, please mark them as helpful. Your participation will help others to find an appropriate solution faster. Thanks for your support!

    Thursday, March 2, 2017 6:43 AM
    Moderator
  • OK, so I added an OR into the formula and got rid of the errors. Also, made some < into <='s to make the logic cover all of the scenarios. It seems to be working. I will test a little more and mark your reply as the answer if I don't encounter any more issues.

    Switch(
    ([% Complete] = 100), "Complete",
    (([% Complete] < 100 OR [% Complete] = 0) And ([Start] - [Baseline Start]) <= 0), "Incomplete",
    ([% Complete] < 100 And ([Start] - [Baseline Start]) > 0), "Behind Schedule")

    Thanks!

    Thursday, March 2, 2017 11:24 PM

All replies

  • Hi,

    if you are using switch, you don't need an additional IIF. So for your second formula, the following should work (you missed some ")" and I removed the IIF:

    Switch(
      ([% Complete] < 100 And ([Start] - [Baseline Start]) < 0), "Incomplete",
      ([% Complete] < 100 And ([Start] - [Baseline Start]) > 0), "Behind Schedule",
      ([% Complete] = 100),"Complete"
       )

    Another suggestion, since you are calculating with Baseline Start is to ensure, that there is a Baseline set, to avoid errors:

    Switch(
     ([% Complete]=100),"Complete",
     (projdatevalue([Baseline Start])=4294967295),"No Baseline",
     ([% Complete]<100 And ([Start]-[Baseline Start])<0),"Incomplete",
     ([% Complete]<100 And ([Start]-[Baseline Start])>0),"Behind Schedule"
          )

    Does that help?
    Barbara


    To increase the value of this forum, please mark the replies that helped to solve your issue as answer. If you find answers to questions from other forum participants to be helpful, please mark them as helpful. Your participation will help others to find an appropriate solution faster. Thanks for your support!

    Thursday, March 2, 2017 6:43 AM
    Moderator
  • Barbara,

    Thank you so much for responding!


    The first formula  you suggested worked, however, as you predicted by supplying the second one, there are still errors. My question now is about the purpose of the projdatevalue line, and the numeric value after the equals sign. Is the value arbitrary, or does it have any meaning? I have tried the first formula and got these results:

    100%	Wed 3/15/17	Wed 3/15/17	0 days	Complete
    0%	Tue 4/11/17	Tue 4/11/17	0 days	#ERROR
    0%	Wed 4/12/17	Wed 4/12/17	0 days	#ERROR
    0%	Tue 4/11/17	Tue 4/11/17	0 days	#ERROR
    86%	Thu 2/23/17	Thu 2/23/17	0 days	#ERROR
    100%	Thu 2/23/17	Thu 2/23/17	0 days	Complete
    100%	Thu 2/23/17	Thu 2/23/17	0 days	Complete
    100%	Thu 2/23/17	Thu 2/23/17	0 days	Complete
    100%	Thu 2/23/17	Thu 2/23/17	0 days	Complete
    100%	Fri 2/24/17	Fri 2/24/17	0 days	Complete
    100%	Fri 2/24/17	Fri 2/24/17	0 days	Complete
    0%	Thu 2/9/17	NA	0 days	Incomplete
    0%	Thu 2/9/17	NA	0 days	Incomplete
    100%	Mon 2/27/17	Mon 2/27/17	0 days	Complete
    0%	Wed 3/15/17	Mon 2/27/17	13 days	Behind Schedule
    0%	Wed 3/15/17	Mon 2/27/17	13 days	Behind Schedule
    0%	Wed 3/15/17	Mon 2/27/17	12 days	Behind Schedule
    0%	Thu 3/16/17	Mon 2/27/17	13 days	Behind Schedule
    96%	Thu 2/9/17	Thu 2/9/17	0 days	#ERROR
    92%	Thu 2/9/17	Thu 2/9/17	0 days	#ERROR
    92%	Thu 2/9/17	Thu 2/9/17	0 days	#ERROR
    100%	Thu 2/23/17	Thu 2/23/17	0 days	Complete
    

    The second formula produces this:

    % Complete	Start	Baseline Start	Start Var.	Task Status (Non Graphical)
    100%	Wed 3/15/17	Wed 3/15/17	0 days	Complete
    0%	Tue 4/11/17	Tue 4/11/17	0 days	#ERROR
    0%	Wed 4/12/17	Wed 4/12/17	0 days	#ERROR
    0%	Tue 4/11/17	Tue 4/11/17	0 days	#ERROR
    86%	Thu 2/23/17	Thu 2/23/17	0 days	#ERROR
    100%	Thu 2/23/17	Thu 2/23/17	0 days	Complete
    100%	Thu 2/23/17	Thu 2/23/17	0 days	Complete
    100%	Thu 2/23/17	Thu 2/23/17	0 days	Complete
    100%	Thu 2/23/17	Thu 2/23/17	0 days	Complete
    100%	Fri 2/24/17	Fri 2/24/17	0 days	Complete
    100%	Fri 2/24/17	Fri 2/24/17	0 days	Complete
    0%	Thu 2/9/17	NA	0 days	No Baseline
    0%	Thu 2/9/17	NA	0 days	No Baseline
    100%	Mon 2/27/17	Mon 2/27/17	0 days	Complete
    0%	Wed 3/15/17	Mon 2/27/17	13 days	Behind Schedule
    0%	Wed 3/15/17	Mon 2/27/17	13 days	Behind Schedule
    0%	Wed 3/15/17	Mon 2/27/17	12 days	Behind Schedule
    0%	Thu 3/16/17	Mon 2/27/17	13 days	Behind Schedule
    96%	Thu 2/9/17	Thu 2/9/17	0 days	#ERROR
    92%	Thu 2/9/17	Thu 2/9/17	0 days	#ERROR
    92%	Thu 2/9/17	Thu 2/9/17	0 days	#ERROR
    100%	Thu 2/23/17	Thu 2/23/17	0 days	Complete
    

    So I do not understand what projdatevalue does, what 4294967295 in the formula represents, and why both formulas still leave me with #ERRORS. 

    Thank you for helping with this! I know this is a silly question, but for someone who has never worked with formulas, and has no way of telling what is causing the errors, your assistance is huge!

    BM

    Thursday, March 2, 2017 9:33 PM
  • OK, so I added an OR into the formula and got rid of the errors. Also, made some < into <='s to make the logic cover all of the scenarios. It seems to be working. I will test a little more and mark your reply as the answer if I don't encounter any more issues.

    Switch(
    ([% Complete] = 100), "Complete",
    (([% Complete] < 100 OR [% Complete] = 0) And ([Start] - [Baseline Start]) <= 0), "Incomplete",
    ([% Complete] < 100 And ([Start] - [Baseline Start]) > 0), "Behind Schedule")

    Thanks!

    Thursday, March 2, 2017 11:24 PM
  • Hi,

    I have to apoligize: I missed the fact, that our original formula did not evaluate ([Start] - [Baseline Start]) = 0. In my test, I skipped this case - sorry. You seemed to be able to solve that b adding an OR.

    So I would write the formulas like follows:

    Formula1:

    Switch(
               ([% Complete]<100 And ([Start]-[Baseline Start])<=0),"Incomplete",
               ([% Complete]<100 And ([Start]-[Baseline Start])>0),"Behind Schedule"
              ,([% Complete]=100),"Complete"
            )

    Formula2:

    Switch(
               ([% Complete]=100),"Complete",
               (projdatevalue([Baseline Start])=4294967295),"No Baseline",
               ([% Complete]<100 And ([Start]-[Baseline Start])<=0),"Incomplete",
               ([% Complete]<100 And ([Start]-[Baseline Start])>0),"Behind Schedule"
              )

    With these formulas, I get the following:

    % Complete Start Baseline Start Formula1 Formula2
    100% Wed 3/15/17 Wed 3/15/17 Complete Complete
    0% Tue 4/11/17 Tue 4/11/17 Incomplete Incomplete
    0% Wed 4/12/17 Wed 4/12/17 Incomplete Incomplete
    0% Tue 4/11/17 Tue 4/11/17 Incomplete Incomplete
    86% Thu 2/23/17 Thu 2/23/17 Incomplete Incomplete
    100% Thu 2/23/17 Thu 2/23/17 Complete Complete
    100% Thu 2/23/17 Thu 2/23/17 Complete Complete
    100% Thu 2/23/17 Thu 2/23/17 Complete Complete
    100% Thu 2/23/17 Thu 2/23/17 Complete Complete
    100% Fri 2/24/17 Fri 2/24/17 Complete Complete
    100% Fri 2/24/17 Fri 2/24/17 Complete Complete
    0% Thu 2/9/17 NA Incomplete No Baseline
    0% Thu 2/9/17 NA Incomplete No Baseline
    100% Mon 2/27/17 Mon 2/27/17 Complete Complete
    0% Wed 3/15/17 Mon 2/27/17 Behind Schedule Behind Schedule
    0% Wed 3/15/17 Mon 2/27/17 Behind Schedule Behind Schedule
    0% Wed 3/15/17 Mon 2/27/17 Behind Schedule Behind Schedule
    0% Thu 3/16/17 Mon 2/27/17 Behind Schedule Behind Schedule
    96% Thu 2/9/17 Thu 2/9/17 Incomplete Incomplete
    92% Thu 2/9/17 Thu 2/9/17 Incomplete Incomplete
    92% Thu 2/9/17 Thu 2/9/17 Incomplete Incomplete
    100% Thu 2/23/17 Thu 2/23/17 Complete Complete

    Regarding your question for "projdatevalue([Baseline Start])=4294967295": This is a language independing check, if a Baseline was set at all. If there is not Baseline, Baseline Start will be displayed as NA in English, NV in German, .... so different letters in different languages. Since I am working in multilanguage environments most of the time, I prefer the check for "NA" as check on its numeric value - and this is the strange "4294967295". It is just the same in all languages and stands for "Not Available".

    If there is no baseline set for a task, it seems better to me, to tell this rather than showing "Incomplete"?

    Regards
    Barbara


    To increase the value of this forum, please mark the replies that helped to solve your issue as answer. If you find answers to questions from other forum participants to be helpful, please mark them as helpful. Your participation will help others to find an appropriate solution faster. Thanks for your support!


    Saturday, March 4, 2017 3:56 PM
    Moderator