Help

Nested IF AND formula

Topic Labels: Formulas
1105 6
cancel
Showing results for 
Search instead for 
Did you mean: 
TailoredTouches
5 - Automation Enthusiast
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

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}

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

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

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

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
)
)

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.