9 Replies Latest reply: May 15, 2014 10:10 AM by Kathi _ RSS

    Getting Operand Count error

    Kathi _

      Hi, all!  I am trying to create a calculated field and continue to receive an operand count error.  I've checked everything that I can think to check but am still getting the error.  What am I doing wrong?  I'm sure it is something I've overlooked but for some reason I'm just not seeing it!  Here is the formula that I've created.  I've put spacing in it so that you can see things like parentheses, etc.

       

      Thanks so much!

       

      if(="Old" .AND.[Yrs of Srvc]<1,1,

      if(="New" .AND.[Yrs of Srvc]<2,1,

      if(="Old" .AND.[Yrs of Srvc]>=1 .AND.[Yrs of Srvc]<5,2,

      if(="New" .AND.[Yrs of Srvc]>=2 .AND.[Yrs of Srvc]<5,2,

      if(>=5  .AND.[Yrs of Srvc]<10,3,

      if(>=10 .AND.[Yrs of Srvc]<15,4,

      if(>=15 .AND.[Yrs of Srvc]<20,5,6),

      )

      )

      )

      )

      )

      ,0)

        • Getting Operand Count error
          RalphB _

          Hi Kathi,

           

          It looks likes it is trying to find the "false" part of your "if" statements and is not finding where it thinks it should be. Try deleting and moving the false statement" ,0"  up to the end of your last if statement  like this " if(>=15 .AND.[Yrs of Srvc]<20,5,6,0) "  and then all your close parentheses.

           

          Give this a try.

           

          Ralph

            • Getting Operand Count error
              Kathi _

              Ralph,

              Thanks for the info.  I tried it and it didn't work.  Any other thoughts?

                • Getting Operand Count error
                  Grant Perkins

                  Kathi,

                   

                  I would be tempted to create each possible calculation to its own field then build the nested IF using the field names. If you need to reduce the number of fields in the model for any reason, once the field name version of the formula works simply substitute the field calcualtion for the field name.

                   

                  You could also avoid the nexted IFs by using the + and separate IFs.

                   

                  That said

                   

                  if(>=15 .AND.[Yrs of Srvc]<20,5,6),))))),0)

                   

                  the last but one , there looks wrong somehow. However it's 1am here and it may not be a great time to be looking at setting soemthing up to experiment! 

                   

                  Maybe in the morning ....

                    • Getting Operand Count error
                      Olly Bond

                      Hello Kathy,

                       

                      One option would be to create two lookup fields, one for the Old schdule and one for the New, based on the possible input values of the Years of Service field (hoping that this is an integer and so only has circa 20 possibilities). Then a simple if(schedule="new", newlookup, oldlookup) should do the job.

                       

                      Hope this helps,

                       

                      Olly

                        • Getting Operand Count error
                          juliejz _

                          that last one does look wrong:

                          if(>=15 .AND.[Yrs of Srvc]<20,5,6),))))),0)

                           

                          I'm reading that last if statement as follows:\

                          if the years of service are between 15 and 20 then assign 5, otherwise assign 6.  The zero seems to be irrelevant, try removing that one?

                            • Getting Operand Count error
                              elginreigner _

                              I see 2 problems in your nested-if. You end with 'if(>=15 .AND.[Yrs of Srvc]<20,5,6),))))),0) ' but the comma after the ')' for the 6 is not needed and nor is the ',0' needed. When building nested-ifs in this manner, you will always end with all parenthesis, in this case ')))))))'

                              • Getting Operand Count error
                                elginreigner _

                                Also:

                                 

                                If you want 0 as the default return if nothing else matches, you will need another if statement to test and return the 6 orelse the return the 0.

                                  • Getting Operand Count error
                                    Steve Caiels

                                    [SIZE=3]Hi Kathi,[/SIZE][/FONT]

                                    [SIZE=3] /SIZE[/FONT]

                                    [SIZE=3]I normally try and keep the layout like this . . /SIZE[/FONT]

                                    [SIZE=3] /SIZE[/FONT]

                                    [SIZE=3]If(Condition,True,

                                    If(Condition,True,

                                    If(Condition,True,

                                    If(Condition,True,

                                    If(Condition,True,

                                    false)))))[/SIZE][/FONT]

                                    [SIZE=3] /SIZE[/FONT]

                                    [SIZE=3]There is a close bracket for each if statement above it.[/SIZE][/FONT]

                                    [SIZE=3] /SIZE[/FONT]

                                    [SIZE=3]So your expression would be . . . /SIZE[/FONT]

                                    [SIZE=3] /SIZE[/FONT]

                                    [SIZE=3]if(="Old" .AND.[Yrs of Srvc]<1,1,

                                    if(="New" .AND.[Yrs of Srvc]<2,1,

                                    if(="Old" .AND.[Yrs of Srvc]>=1 .AND.[Yrs of Srvc]<5,2,

                                    if(="New" .AND.[Yrs of Srvc]>=2 .AND.[Yrs of Srvc]<5,2,

                                    if(>=5 .AND.[Yrs of Srvc]<10,3,

                                    if(>=10 .AND.[Yrs of Srvc]<15,4,

                                    if(>=15 .AND.[Yrs of Srvc]<20,5,

                                    6)))))))[/SIZE][/FONT]

                                    [SIZE=3] /SIZE[/FONT]

                                    [SIZE=3]Regards,[/SIZE][/FONT]

                                    [SIZE=3]Steve.[/SIZE][/FONT]

                                      • Getting Operand Count error
                                        Kathi _

                                        Oh my gosh!  I just realized that I never responded to all the assistance!  I did find an error in the parenthese after many curse words.  Thanks to all that provided input. 

                                        Again, I apologize for the tardiness of my heartfelt thanks!