The Airtable Community will undergo scheduled maintenance on September 17 from 10:00 PM PST to 11:15 PM PST. During this period, you may experience temporary disruptions. We apologize for any inconvenience and appreciate your understanding.

Conditional Formulas for VAT

Topic Labels: Base design Data Formulas
Solved
1596 4
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

Hi,

I'm new to airtable and am trying to get a simple conditional VAT formula to work. I feel like I am circling the solution but I'm not quite there.

Essentially I am trying to track various hires and purchases for work, some of which have VAT and some do not. I have a Gross cost but I need the NET cost (without VAT).

I currently have a VAT Field that is a single select field with options None and 20%. I'm trying to write a conditional formula for the NET Cost that is based on the VAT field and essentially works as follows:

IF {VAT="NONE", Gross Amount

IF {VAT="20%", (GROSS Amount/120)*100

I keep getting invalid formula and error messages and I'm not quite sure what I'm missing.

Any help greatly appreciated!

1 Solution

Accepted Solutions
9 - Sun

Hey @cevison2,

The error in your formula is the missing closing parenthesis ")" at the end.

PS Currency field and number field are the same data type number so this will never effect you

Thanks

Yours sincerely,
Dimitris Goudis

4 Replies 4
9 - Sun

Hey @cevison2

Welcome to Airtable and Airtable community 😊

- Your fields {GROSS Amount} has to be a number field type
- Please check the typo for VAT field values because the capital text matters

And because you have only 2 vat options you could use the following formula:

if( {VAT}="20%", {Gross}*(1+0.20),
{Gross}
)

If you add more VAT values I would recommend to replace the if formula with the switch.

For any kind of question please do not hesitate to text us back.

Yours sincerely,
Dimitris Goudis

5 - Automation Enthusiast

Hi Dimitris,

Definitely good to know the capitals matter too!

Unfortunately it's still saying I have an invalid formula. I've attached a couple of screenshots below of the table. I currently have GROSS Cost as a Currency field rather than a Number field, is that ok?

In the screenshot the Net cost formula is currently set to just calculate as if the VAT is 20%.

Thank you

9 - Sun

Hey @cevison2,

The error in your formula is the missing closing parenthesis ")" at the end.

PS Currency field and number field are the same data type number so this will never effect you

Thanks

Yours sincerely,
Dimitris Goudis

5 - Automation Enthusiast

Amazing!

That's fixed it, I must have missed that when I copy pasted and just didn't notice. Thank you so much!