Skip to main content

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!

 

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

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


Hi Dimitris, 

Thank you so much for your reply.

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

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

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


Amazing!

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

Reply


Terms & Conditions