Need formula to add VAT in conditions

jimmmi

Board Regular
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.

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
=IF(B2<>"Export Invoice",E2*1.13,E2)

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"))

Thank you! Actually this worked.

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

Thanks! this actually worked!

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?

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.

Last edited:
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?

Last edited:
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?

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.

Replies
1
Views
209
Replies
6
Views
173
Replies
1
Views
148
Replies
6
Views
358
Replies
6
Views
128

1,196,306
Messages
6,014,563
Members
441,828
Latest member
cofracr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back