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]




        • 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,



              • 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!