Nested IIF Statements....with a bit of an AND

[cyd000]

I have the following Excel formula (that works fine) that I need to convert in Access 2013:

=IF(OR(G21<80%,J22<70%),0,IF(J22<80%,80%+(J22-70%)*(1.0%*100),IF(J22<90%,50%+(J22-80%)*(1.5%*100),IF(J22<100%,65%+(J22-90%)*(3.5%*100),IF(J22=1,1,1+(J22-100%)*(2.0%*100))))))

This is the expression I came up with for Access....but it doesn't work. I am getting the error that I have used a comma improperly.

1IncentPayout:IIF([1TotPerfToGoal]<80,and[1WeightAvPerf]<70),0,IIF([1WeightAvPerf]<80,40+(WeightAvPerf-70])*(1*100),IIF([1WeightAvPerf]<90,50+([1WeightAvPerf-80])*(1.5*100),IIF([1WeightAvPerf]<100,65+([1WeightAvPerf-90])*(3.5*100),IIF([1WeightAvPerf]=1,1,1+([1WeightAvPerf]-100)*(2*100)))))))

I have no idea if i am doing this even remotely right. I haven't worked in Access in 10 years.

Any help would be greatly appreciated!

Thanks!
Cyd

 

[mathematician]

I have the following Excel formula (that works fine) that I need to convert in Access 2013:

=IF(OR(G21<80%,J22<70%),0,IF(J22<80%,80%+(J22-70%)*(1.0%*100),IF(J22<90%,50%+(J22-80%)*(1.5%*100),IF(J22<100%,65%+(J22-90%)*(3.5%*100),IF(J22=1,1,1+(J22-100%)*(2.0%*100))))))

This is the expression I came up with for Access....but it doesn't work. I am getting the error that I have used a comma improperly.

1IncentPayout:IIF([1TotPerfToGoal]<80,and[1WeightAvPerf]<70),0,IIF([1WeightAvPerf]<80,40+(WeightAvPerf-70])*(1*100),IIF([1WeightAvPerf]<90,50+([1WeightAvPerf-80])*(1.5*100),IIF([1WeightAvPerf]<100,65+([1WeightAvPerf-90])*(3.5*100),IIF([1WeightAvPerf]=1,1,1+([1WeightAvPerf]-100)*(2*100)))))))

I have no idea if i am doing this even remotely right. I haven't worked in Access in 10 years.

Any help would be greatly appreciated!

Thanks!
Cyd

- I have red highlighted a column with a missed leading "1". Insert it.
- In excel you were using OR, ideally in your access query it should be OR also. I have highlighted your AND in access query.
- instead of ",and" put " OR " note there are spaces before and after OR.

You may consider copying and replacing using the already edited below:

- Ideally if you made a mistake in excel by putting OR instead of AND just insert AND in place of OR below. Remember to leave spaces before and after AND/OR, thats how it works in Access.

1IncentPayout: IIF([1TotPerfToGoal]<80 OR [1WeightAvPerf]<70,0,IIF([1WeightAvPerf]<80,80+([1WeightAvPerf]-70)*(1*100),IIF([1WeightAvPerf]<90,50+([1WeightAvPerf]-80)*(1.5*100),IIF([1WeightAvPerf]<100,65+([1WeightAvPerf]-90)*(3.5*100),IIF([1WeightAvPerf]=1,1,1+([1WeightAvPerf]-100)*(2*100))))))

 

[cyd000]

- I have red highlighted a column with a missed leading "1". Insert it.
- In excel you were using OR, ideally in your access query it should be OR also. I have highlighted your AND in access query.
- instead of ",and" put " OR " note there are spaces before and after OR.

You may consider copying and replacing using the already edited below:

- Ideally if you made a mistake in excel by putting OR instead of AND just insert AND in place of OR below. Remember to leave spaces before and after AND/OR, thats how it works in Access.

1IncentPayout: IIF([1TotPerfToGoal]<80 OR [1WeightAvPerf]<70,0,IIF([1WeightAvPerf]<80,80+([1WeightAvPerf]-70)*(1*100),IIF([1WeightAvPerf]<90,50+([1WeightAvPerf]-80)*(1.5*100),IIF([1WeightAvPerf]<100,65+([1WeightAvPerf]-90)*(3.5*100),IIF([1WeightAvPerf]=1,1,1+([1WeightAvPerf]-100)*(2*100))))))

Thank you SO much, this worked great!