Skip to main content
Solved

Using if formula to calculate prices based on various features

  • July 6, 2020
  • 6 replies
  • 78 views
J


I’m trying to calculate a formula using the if function but not sure where to start. My formula correctly calculates the wholesale cost of a case of 12 bottles of imported wine from the ex cellar price. However, still wines under 14.10% Alc are taxed at 25% of the value (ex cellar x 1.25), still wines 14.1% and higher are not taxed, and to make things even more exciting, sparkling wines are never taxed. I want to make one formula that incorporates whether the wine is taxed or not. can anyone help?

in my formula: ex cellar = price of 1 bottle of wine in EUR, 1.15 = est exchange rate 1 EUR= USD, 12 = number of bottles in a case, 18 = cost of shipping in USD to import the wine, 0.6 = target 40% margin on final price.

everything works fine, just not sure how to incorporate the tariff on some wines but not others.

Best answer by Mohamed_Swella1

Thanks again for taking the time to help me out - we’re working on a big proposal and this is quite important to get right.

Unfortunately, the OR formula didn’t work (it returned the same value for wine over 14% or under 14%).

Talking with my team, it’s actually preferable to have a tax exempt checkbox because we don’t always have the alc % label information, but we do know if the wine is tax exempt or not. What would a formula look like with that tax exempt checkbox?

I also created a sparkling item, with all items at the same ex cellar cost (3 EUR). See screenshots below to help as well:


In this case, the formula would be

IF({Tariff Exempt}, (({Ex Cellar Distributor} *1.15* 12)+18)/.6, (({Ex Cellar Distributor} *1.15* 12*1.25)+18)/.6)

6 replies

Mohamed_Swella1
Forum|alt.badge.img+17

Hi @Joshua_Adler,

Welcome to Airtable Community! :slightly_smiling_face:

You can have an IF formula for the Alc % Label, so your formula would become:

IF({Alc % Label}>=0.141, “Formula” * taxes, “Formula”)

I havent included the formula or taxes since Im not sure whether you want to add the taxes after the exchange rate and shipping costs etc or before.

If this helps you, please mark it as Solution so others can see it.

BR,
Mo

J
  • Joshua_Adler
  • Author
  • New Participant
  • July 7, 2020

thank you for the quick reply. The problem is that formula doesn’t take into account whether the wine is still or sparkling, so sparkling over 14.1% would still be taxed (I use taxed and tariffed to mean the same thing in my example here).

Maybe a simpler solution is to add a column “Tariff exempt”, but I can’t get this formula to work - note I included the 25% tax applied directly to the ex cellar distributor:

IF({Tariff Exempt} is TRUE,TRUE(({Ex Cellar Distributor}1.1512)+18)/.6),FALSE(({Ex Cellar Distributor}1.1512*1.25)+18)/.6)

Mohamed_Swella1
Forum|alt.badge.img+17

thank you for the quick reply. The problem is that formula doesn’t take into account whether the wine is still or sparkling, so sparkling over 14.1% would still be taxed (I use taxed and tariffed to mean the same thing in my example here).

Maybe a simpler solution is to add a column “Tariff exempt”, but I can’t get this formula to work - note I included the 25% tax applied directly to the ex cellar distributor:

IF({Tariff Exempt} is TRUE,TRUE(({Ex Cellar Distributor}1.1512)+18)/.6),FALSE(({Ex Cellar Distributor}1.1512*1.25)+18)/.6)


In this case, you can add an OR argument to the IF. So the formula would become

IF(OR({Alc % Label}>=0.141,Find("sparkling",Type)>0), (({Ex Cellar Distributor} *1.15* 12)+18)/.6, (({Ex Cellar Distributor} *1.15* 12*1.25)+18)/.6)

I think this would be better than the Tax Exempt checkbox as it will be subject to human error.

J
  • Joshua_Adler
  • Author
  • New Participant
  • July 7, 2020

In this case, you can add an OR argument to the IF. So the formula would become

IF(OR({Alc % Label}>=0.141,Find("sparkling",Type)>0), (({Ex Cellar Distributor} *1.15* 12)+18)/.6, (({Ex Cellar Distributor} *1.15* 12*1.25)+18)/.6)

I think this would be better than the Tax Exempt checkbox as it will be subject to human error.


Thanks again for taking the time to help me out - we’re working on a big proposal and this is quite important to get right.

Unfortunately, the OR formula didn’t work (it returned the same value for wine over 14% or under 14%).

Talking with my team, it’s actually preferable to have a tax exempt checkbox because we don’t always have the alc % label information, but we do know if the wine is tax exempt or not. What would a formula look like with that tax exempt checkbox?

I also created a sparkling item, with all items at the same ex cellar cost (3 EUR). See screenshots below to help as well:

Mohamed_Swella1
Forum|alt.badge.img+17

Thanks again for taking the time to help me out - we’re working on a big proposal and this is quite important to get right.

Unfortunately, the OR formula didn’t work (it returned the same value for wine over 14% or under 14%).

Talking with my team, it’s actually preferable to have a tax exempt checkbox because we don’t always have the alc % label information, but we do know if the wine is tax exempt or not. What would a formula look like with that tax exempt checkbox?

I also created a sparkling item, with all items at the same ex cellar cost (3 EUR). See screenshots below to help as well:


In this case, the formula would be

IF({Tariff Exempt}, (({Ex Cellar Distributor} *1.15* 12)+18)/.6, (({Ex Cellar Distributor} *1.15* 12*1.25)+18)/.6)

J
  • Joshua_Adler
  • Author
  • New Participant
  • July 7, 2020

Thank you so much for your help! that worked.

Mohamed_Swella1
Terms & ConditionsAccessibility statement