Skip to main content

Nested IF AND formula

  • December 5, 2021
  • 6 replies
  • 55 views
T

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

Kamille_Parks11
Forum|alt.badge.img+27

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}
ScottWorld
Justin_Barrett
T
  • TailoredTouches
  • Author
  • Participating Frequently
  • December 8, 2021

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

T
  • TailoredTouches
  • Author
  • Participating Frequently
  • December 11, 2021

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

Kamille_Parks11
Forum|alt.badge.img+27

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

Hannah_Wiginton
kuovonne
T
  • TailoredTouches
  • Author
  • Participating Frequently
  • December 11, 2021

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

Kamille_Parks11
Forum|alt.badge.img+27

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.

Justin_Barrett
Terms & ConditionsAccessibility statement