3 Replies Latest reply: May 15, 2014 10:06 AM by glennes _ RSS

    Help with Nested IF with .AND.

    glennes _

      I cannot get this formula to run. I get either an invalid delimiter error or an operand count error depending on how I structure it. As posted below, I get an invalid delimiter error.

       

      IF( = 5, Round(*2.5) +/SIZE

      IF(<5 .AND. >=4),ROUND(*2.25) +/SIZE

      IF(<4 .AND. >=3),ROUND(*2) +/SIZE

      IF(<3 .AND. >=2),ROUND(*1.5) +/SIZE

      IF(<2 .AND. >=1),ROUND(*1),0),0),0),0) /SIZE

       

      If I do it this way, I get an operand count error:[/SIZE]

       

      [SIZE=2]IF( = 5, Round(*2.5,0) +/SIZE

      IF(<5 .AND. >=4),ROUND(*2.25,0)+/SIZE

      IF(<4 .AND. >=3),ROUND(*2,0) +/SIZE

      IF(<3 .AND. >=2),ROUND(*1.5,0) +/SIZE

      IF(<2 .AND. >=1),ROUND(*1,0))[/SIZE]

       

      How do I fix it so that it will run properly?[/SIZE]

      Send reply to: [EMAIL="glenn_91@msn.com"]glenn_91@msn.com[/EMAIL][/SIZE]

       

      Thanks![/SIZE]

      /SIZE

        • Help with Nested IF with .AND.
          RalphB _

          Hi glennes,

           

          You have one too many right parentheses in each if statement.  Try removing the right parentheses that you have in the middle of your if statements.  For example, change

           

          IF(<5 .AND. >=4),ROUND(*2.25) to

          IF(<5 .AND. >=4,ROUND(*2.25) .

          And remove all but one of the ",0)" at the end in the first statement you posted.

           

          Give that a try and let us know if that works.

          • Help with Nested IF with .AND.
            Olly Bond

            Hello glennes,

             

            To make life easy, try defining a field called as int().

             

            Now create another calculated field of type lookup , called , as a lookup field based on the values of . It should be straightforward that way, and I promise you won't have to count any pairs of brackets or worry about AND and OR conditions...

             

            Best wishes,

             

            Olly

              • Help with Nested IF with .AND.
                glennes _

                This is what finally worked:

                 

                IF(>=5,(100/[Ded Rate]).025,

                IF(>=4,(100/[Ded Rate])0.0225,

                IF(>=3,(100/[Ded Rate])0.02,

                IF(>=2,(100/[Ded Rate])0.015,

                IF(>=1,(100/[Ded Rate])0.01,[Amount])))))

                 

                Next time I need to do something like this, though, I'm going to use the lookup table approach that was recommended above.

                 

                Thanks so much for your help!

                /SIZE