Need to subtract vat from gross amount.

[buachille]

Hi,

I have created a calculator in excel for the purpose of quickl
checking whether people who have employed us as a subcontractor hav
deducted the correct amount of cis tax, but there is one thing i can
get quite right.

I want to be able to enter the gross amount in the top cell, and hav
excel show me in the cell below what the original net amount of ou
invoice was. For example:

We did a contract for £400.

The vat added took this up to £470.

I want to be able to type in £470 in the top cell and excel will sho
£400 in the cell below.

I did discover that i could do this by using this formula =B5*0.14894

But i've noticed that for bigger figures it does not round up properly
I've tried to find the correct number to do this but i'm struggling.

There must be an easier way. The other thing i tried was =B5-17.5% bu
i quickly realised that this does not work at all.

Can anyone help. I'm sure this is very straightforward for people wh
know their stuff. I am definitely not an advanced excel user!!!
:confused

 

[Sandy Mann]

buachille,

Try:

=IF(A1="","",ROUND(A1/1.175,2))

or with 1.175 in C1

=IF(A1="","",ROUND(A1/C1,2))

So that if the VAT rate changes you don't have to change all the individual
formulas

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[buachille]

many thanks :