Could someone please help me with my nested IIF statement? I am getting different errors for i such as "wrong number of arguments" and "missing bracket or closing parentheses." When I start adding parentheses then it will say wrong # of arguments. Any help would be much appreciated!
Thank you!
=IIf([planRate]![anytimeMinutes]<=300,35,35+(.50*[planRate]![additionalMinutes]),IIf([planRate]![anytimeMinutes]>300 And [planRate]![anytimeMinutes]<=500,40,40+(.45* [planRate]![additionalMinutes]), IIf([planRate]![anytimeMinutes]>500 And [planRate]![anytimeMinutes]<=700,50,50+(.40* [planRate]![additionalMinutes]),IIf([planRate]![anytimeMinutes]>700 And [planRate]![anytimeMinutes]<=1100,65,65+(.35* [planRate]![additionalMinutes]), IIf([planRate]![anytimeMinutes]>1400 And [planRate]![anytimeMinutes]<=2000,100,100+(.25* [planRate]![additionalMinutes]),IIf([planRate]![anytimeMinutes]>2000 And [planRate]![anytimeMinutes]<=2500,115,115+(.20* [planRate]![additionalMinutes]))))))
The issue with your nested IIF statement is that you have misplaced some closing parentheses. Here is your corrected formula:
=IIf([planRate]![anytimeMinutes]<=300,35,35+(.50*[planRate]![additionalMinutes]),IIf([planRate]![anytimeMinutes]>300 And [planRate]![anytimeMinutes]<=500,40,40+(.45*[planRate]![additionalMinutes]), IIf([planRate]![anytimeMinutes]>500 And [planRate]![anytimeMinutes]<=700,50,50+(.40*[planRate]![additionalMinutes]),IIf([planRate]![anytimeMinutes]>700 And [planRate]![anytimeMinutes]<=1100,65,65+(.35*[planRate]![additionalMinutes]), IIf([planRate]![anytimeMinutes]>1400 And [planRate]![anytimeMinutes]<=2000,100,100+(.25*[planRate]![additionalMinutes]),IIf([planRate]![anytimeMinutes]>2000 And [planRate]![anytimeMinutes]<=2500,115,115+(.20*[planRate]![additionalMinutes])))))))
In this corrected formula, I've made sure that each IIF function has the correct number of arguments and that all opening and closing parentheses match properly.
To avoid these kind of issues, it's a good practice to format your nested IIF statements in a more organized manner. Here's an example:
=IIf([planRate]![anytimeMinutes] <= 300,
35,
35 + (.50 * [planRate]![additionalMinutes]),
IIf([planRate]![anytimeMinutes] > 300 And [planRate]![anytimeMinutes] <= 500,
40,
40 + (.45 * [planRate]![additionalMinutes]),
IIf([planRate]![anytimeMinutes] > 500 And [planRate]![anytimeMinutes] <= 700,
50,
50 + (.40 * [planRate]![additionalMinutes]),
IIf([planRate]![anytimeMinutes] > 700 And [planRate]![anytimeMinutes] <= 1100,
65,
65 + (.35 * [planRate]![additionalMinutes]),
IIf([planRate]![anytimeMinutes] > 1400 And [planRate]![anytimeMinutes] <= 2000,
100,
100 + (.25 * [planRate]![additionalMinutes]),
IIf([planRate]![anytimeMinutes] > 2000 And [planRate]![anytimeMinutes] <= 2500,
115,
115 + (.20 * [planRate]![additionalMinutes])
)
)
)
)
)
)
By properly indenting and aligning the nested IIF statements, it becomes easier to spot any errors or missing parentheses.