# Nested IF AND formula

Topic Labels: Formulas
1163 6
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

Help! I keep pasting this formula into Airtable, and it deletes it after the 5 Item section. Even if I can get it to “take” the 7 and 10 item part of the formula, it says there’s an error. Where is the error?

IF(AND({Number of Items}=5, {VA Recipient?}),((65-{Item Discount})*1.06)+{Shipping Total Cust. Paid}, IF({Number of Items}=5, 65-{Item Discount}+{Shipping Total Cust. Paid}), IF(AND({Number of Items}=7, {VA Recipient?}),((85-{Item Discount})*1.06)+{Shipping Total Cust. Paid}, IF({Number of Items}=7, 85-{Item Discount}+{Shipping Total Cust. Paid}), IF(AND({Number of Items}=10, {VA Recipient?}),((100-{Item Discount})*1.06)+{Shipping Total Cust. Paid}, IF({Number of Items}=10, 100-{Item Discount}+{Shipping Total Cust. Paid}))))

This is trying to say: If there are 5 items and a VA Recipient, then do X. If there are 5 items and NOT a VA Recipient, then do y. Then it repeats for 7 items and 10 items.

Thoughts? Thank you all!!!

6 Replies 6
16 - Uranus

The formula is long and repititive which makes finding what the error might be difficult. For simplification purposes I reorganized your formula to use SWITCH() statements instead:

``````SWITCH(
{Number of Items},
5, IF({VA Recipient?}, ((65-{Item Discount})*1.06)+{Shipping Total Cust. Paid}, 65-{Item Discount}+{Shipping Total Cust. Paid}),
7, IF({VA Recipient?}, ((85-{Item Discount})*1.06)+{Shipping Total Cust. Paid}, 85-{Item Discount}+{Shipping Total Cust. Paid}),
10, IF({VA Recipient?}, ((100-{Item Discount})*1.06)+{Shipping Total Cust. Paid}, 100-{Item Discount}+{Shipping Total Cust. Paid})
)
``````

That makes the logic pattern much more clear. So the formula can be further simplified like this:

``````(
SWITCH({Number of Items}, 5, 65, 7, 85, 10, 100) -
{Item Discount}
) *
IF({VA Recipient?}, 1.06, 1) +
{Shipping Total Cust. Paid}
``````
5 - Automation Enthusiast

Oh this is so much easier! Thank you SO MUCH!!!

5 - Automation Enthusiast

If you happen to be able to help me again, I’m trying to get this one…

Right now, another formula is:
IF({Number of Items}=5,45,IF({Number of Items}=7,60,IF({Number of Items}=10, 75)))

I want to add that this is correct before 10/01/2021, but after that date, it’s:
IF({Number of Items}=5,50,IF({Number of Items}=7,65,IF({Number of Items}=10, 80)))

I tried this, but something is wrong…

SWITCH(
IF(IS_BEFORE({Date}, “2021-10-01”), 1, IF({Date}, 2)),
1, SWITCH(
{Number of Items},
5, 45,
7, 60,
10, 75,
3, SWITCH(
{Number of Items},
5, 50,
7, 65,
10, 80,
)
)

16 - Uranus

You have closing parentheses and commas in the wrong places. There should be a closing paranthesis after “75”, and there should be no comma after “80”.

Also your IF() will output either 1 or 2, but your SWITCH seems to analyze either 1 or 3

5 - Automation Enthusiast

Thank you so much. It is still not working for some reason. Any other thoughts? Also, thank you SO much for your help. Really appreciate it.

SWITCH(
IF(IS_BEFORE({Date}, “2021-10-01”), 1, IF({Date}, 2)),
1, SWITCH(
{Number of Items},
5, 45,
7, 60,
10, 75)
2, SWITCH(
{Number of Items},
5, 50,
7, 65,
10, 80,
15, 120
)
)

16 - Uranus

you need a comma after “75)”. If that isn’t it you need to be more specific than “its not working”. You’re getting an error? It won’t let you save the formula? The formula saves but the result is wrong? etc.