Skip to main content
Solved

Discount formula and multiple functions

  • September 28, 2021
  • 3 replies
  • 47 views
J

Hello everyone! I’m trying to migrate from Google Sheets to Airtable, but still having issues creating a budget template for my projects…

As you can see in the screenshot below, my “total” and “tax” fields are working fine when I add numbers to the fields “QTY” and “COST”, however, my “SUBTOTAL” formula (also in the screenshot) always stays at zero, regardless if I do or do not add numbers to the fields “freight” or “Discount”.
I also would like my subtotal field to calculate the formulas even if “discounts” and “freight” are numbered as zero as I don’t always get a discount or have to pay for freight.

Can someone please help me understand what I’m doing wrong and how to solve it? It would be very much appreciated!

Best answer by Dimitris_Goudis

Hi @Jahsun_Daher_Golding ,

Welcome to Airtable community.

First of all the discount field includes already numbers in percent formatting. So If you want to apply this discount the sum of {total} + {tax} + {freight}, you shouldn’t divide the discount again with 100. The correct the formula for the subtotal is:

({total} + {tax} + {freight})*(1-{discount})

The following formula returns the value of discount:

({total} + {tax} + {freight})*({discount})

Both formulas work even freight or discount is equal to zero.

Please text us back if this solved your problem.

Thanks

3 replies

Dimitris_Goudis
Forum|alt.badge.img+20

Hi @Jahsun_Daher_Golding ,

Welcome to Airtable community.

First of all the discount field includes already numbers in percent formatting. So If you want to apply this discount the sum of {total} + {tax} + {freight}, you shouldn’t divide the discount again with 100. The correct the formula for the subtotal is:

({total} + {tax} + {freight})*(1-{discount})

The following formula returns the value of discount:

({total} + {tax} + {freight})*({discount})

Both formulas work even freight or discount is equal to zero.

Please text us back if this solved your problem.

Thanks

J
J
  • Jahsun_Daher_Go
  • Author
  • New Participant
  • September 28, 2021

Hi @Jahsun_Daher_Golding ,

Welcome to Airtable community.

First of all the discount field includes already numbers in percent formatting. So If you want to apply this discount the sum of {total} + {tax} + {freight}, you shouldn’t divide the discount again with 100. The correct the formula for the subtotal is:

({total} + {tax} + {freight})*(1-{discount})

The following formula returns the value of discount:

({total} + {tax} + {freight})*({discount})

Both formulas work even freight or discount is equal to zero.

Please text us back if this solved your problem.

Thanks


Hi @Dimitris_Goudis
Thank you so so much for this
The first formula worked for me!
The second one did not calculate the subtotal if a discount isn’t applied…
I really appreciate your time and help.
Thank you.

Dimitris_Goudis
Forum|alt.badge.img+20

Hi @Dimitris_Goudis
Thank you so so much for this
The first formula worked for me!
The second one did not calculate the subtotal if a discount isn’t applied…
I really appreciate your time and help.
Thank you.


That’s good!
For the second case, if the {% discount}=0 then the result should be 0. This is the value of the discount.
Isn’t it something you want?

Terms & ConditionsAccessibility statement