Need formula to add VAT in conditions

[jimmmi]

Hello i need a formula which adds VAT 13% if a specific word entered in cell.
In red i explain what i need. I have to type of invoices, one that i add VAT 13% and the other which i don't.
If i add the word "export invoice" in B3, i want the E3 total to just copy to F3.
I i add the word normal Invoice like B2 i want the formula to copy and add 13% to F2.
Help me please

 

[Dave Patton]

=IF(B2<>"Export Invoice",E2*1.13,E2)

 

[konew1]

I suggest you apply data validation to column B where you enter the invoice type. Dave's formula will only work if you are 100% consistent with data entry in B.
An alternative could be =IF(b2="Export Invoice",E2,IF(B2="Normal Invoice",E2*1.13,"Check invoice type"))

 

[jimmmi]

Thank you! Actually this worked.

 

[jimmmi]

ADVERTISEMENT

=IF(B2<>"Export Invoice",E2*1.13,E2)

Thanks! this actually worked!

 

[jimmmi]

I suggest you apply data validation to column B where you enter the invoice type. Dave's formula will only work if you are 100% consistent with data entry in B.
An alternative could be =IF(b2="Export Invoice",E2,IF(B2="Normal Invoice",E2*1.13,"Check invoice type"))

Dave's formula worked with some regional modifications.
What the difference between Dave's and your formula?

 

[Zakkaroo]

ADVERTISEMENT

Dave's formula says that if B2 does NOT equal 'Export Invoice' then apply VAT, otherwise, do not apply VAT. It's very efficient, but is literally only checking to see whether B2 says Export Invoice or not, any slight variation will cause problems. If you know there will never be variation, this formula is perfect for your needs.

Konew1's formula says that if B2 is equal to Export Invoice, do not apply VAT. If B2 is equal to 'Normal Invoice' then apply VAT - If B2 is anything else, show the phrase 'Check Invoice Type'

What Konew1 is saying is that if you or someone else accidentally typed Expot Invoice instead of Export Invoice .... or Export Invioce, or Export Invoice. (with a full stop at the end) etc. then Dave's formula will not return the right value. So you have to be very careful that it will definitely always say 'Export Invoice' and a way of doing this, is forcing users to select 'Export' or 'Normal' from a drop down menu (data validation)

In your example data, for Cherries, imagine it is supposed to be an export invoice but someone accidentally typed 'Expot Invoice'

The correct answer would be 37,5
Daves formula would return 42,375
Konew1's formula would return 'Check Invoice Type'

With Dave's formula, you would believe everything was working correctly, but you've accidentally applied VAT, just by mispelling Export.

 

[jimmmi]

Dave's formula says that if B2 does NOT equal 'Export Invoice' then apply VAT, otherwise, do not apply VAT. It's very efficient, but is literally only checking to see whether B2 says Export Invoice or not, any slight variation will cause problems. If you know there will never be variation, this formula is perfect for your needs.

Konew1's formula says that if B2 is equal to Export Invoice, do not apply VAT. If B2 is equal to 'Normal Invoice' then apply VAT - If B2 is anything else, show the phrase 'Check Invoice Type'

What Konew1 is saying is that if you or someone else accidentally typed Expot Invoice instead of Export Invoice .... or Export Invioce, or Export Invoice. (with a full stop at the end) etc. then Dave's formula will not return the right value. So you have to be very careful that it will definitely always say 'Export Invoice' and a way of doing this, is forcing users to select 'Export' or 'Normal' from a drop down menu (data validation)

In your example data, for Cherries, imagine it is supposed to be an export invoice but someone accidentally typed 'Expot Invoice'

The correct answer would be 37,5
Daves formula would return 42,375
Konew1's formula would return 'Check Invoice Type'

With Dave's formula, you would believe everything was working correctly, but you've accidentally applied VAT, just by mispelling Export.

Hello i tried =IF(b2="Export Invoice",E2,IF(B2="Normal Invoice",E2*1.13,"Check invoice type"))

but i get error in export invoice, the check invoice type and normal invoice works good, it seems that the problem is in b2="Export Invoice",E2,
any ideas?

 

[jimmmi]

Hello i tried =IF(b2="Export Invoice",E2,IF(B2="Normal Invoice",E2*1.13,"Check invoice type"))

but i get error in export invoice, the check invoice type and normal invoice works good, it seems that the problem is in b2="Export Invoice",E2,
any ideas?

ok i found the problem and fixed it, but the bigger problem now is that if i dont enter and invoice type in all cells, it gives me Check Invoice Type instead of -€.
This is wrong.
Any idea to fix it?

 

[Zakkaroo]

Do you want a third condition then, that says if the Invoice Type is blank, show xx ?

If so, you could have:

=IF(B2="","PUT WHAT YOU WANT TO HAPPEN HERE",IF(B2="Export Invoice",E2,IF(B2="Normal Invoice",E2*1.13,"Check invoice type")))

The 'Put what you want to happen here' bit should be replaced by whatever you want ... that might be "-€" or it might be E2*0 or "" etc.