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

Help with Nested IF with .AND.

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]

Thanks![/SIZE]

/SIZE

• Help with Nested IF with .AND.

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.

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.

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