excel spreadsheets- how to calculate VAT

[evedawn]

how do you get an excel spreadsheet to automatically add vat to a numerical amount?

thanks ina dvance

[EmEd1984]

this might be a longer winded way of doing it, but this is how i work it! say your net figure is in cell B1, then the gross calculation in cell c1 would be:

=SUM(b1/100)*117.5

hope that makes sense!

[chelle7272]

Thats how I do it too

[evedawn]

thank you to both

i will try it next time.

[ACtheTROLL]

bored - so complicated things ... sort of
a small refinement is to enter the vat rate in a cell and refer to that - so if the rate changes ... or if you deal with more than one rate ... your total column isn't complicated

3 columns titled - price(A), vat(B) and total(C)
start with the vat rate in e1 (17.5)
in a2 enter the amount (�100.00)
in b2 calc VAT (=A2*$E$1/100)
in c2 calc total (=A2+B2)

total only
start with the vat rate in e1 (17.5)
in a2 enter the amount (�100.00)
in c2 calc VAT (=A2*($E$1+100)/100)

vat ONLY
start with the vat rate in e1 (17.5)
in a2 enter the amount (�100.00)
in b2 calc vat (=A10*$E$1/100)

vat charged on an item from price
in a2 enter the amount (�100.00)
in b2 calc vat (=(A2*$E$1)/A2)

get the idea?

if you are actually keeping books ... this way you can easily mod the sheet if the rate changes -
and easily arrange the sheet to total your amounts.

[fo3nix]

As the above says, put a value in some cell at the top. This is your VAT value.

Then just multiply whatever other cells you want by this number to get the VAT amount. Put the letter of the VAT cell inside some dollar signs so that it's always placed there (and won't change if you do a fill), setting is as a constant.

For example in cell A1, put the value of 1.175 in (so that you don't have to divide by 100 every time you want to use it).

In cell G15, you could have something like =F15 * $A$1

Then G15 would contain the VAT-added value of F15.

[joggerjayne]

This is all so clever.

[evedawn]

"KIND OF" getting this to work...thanks all of you for your answers...

I can get it to work horizontally - but not if i try do this vertically? what am i doing wrong?


I need it cos I want to set up a template for invoices using excel.

[EmEd1984]

you just need to move the calculation to the right cell, don't worry.

imagine the net price is in cell a1
then in cell a2 you would put the calculation =sum(a1/100)*117.5

OR

as it's an invoice, you'll need to state what the amount of vat is...

net total in cell a1

vat value in cell a2
=sum(a1/100)*17.5

gross price in cell a3
=sum(a1+a2)

hope that helps