Need to subtract vat from gross amount.

Hi,

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

I want to be able to enter the gross amount in the top cell, and have excel show me in the cell below what the original net amount of our 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 show £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% but i quickly realised that this does not work at all.

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

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
[email protected]
Replace@mailinator with @tiscali.co.uk


"buachille" <[email protected]> wrote
in message news:[email protected]...
>
> Hi,
>
> I have created a calculator in excel for the purpose of quickly
> checking whether people who have employed us as a subcontractor have
> deducted the correct amount of cis tax, but there is one thing i cant
> get quite right.
>
> I want to be able to enter the gross amount in the top cell, and have
> excel show me in the cell below what the original net amount of our
> 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 show
> £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% but
> i quickly realised that this does not work at all.
>
> Can anyone help. I'm sure this is very straightforward for people who
> know their stuff. I am definitely not an advanced excel user!!!!
>
>
>
> --
> buachille
> ------------------------------------------------------------------------
> buachille's Profile:
> http://www.excelforum.com/member.php...o&userid=19523
> View this thread: http://www.excelforum.com/showthread...hreadid=468892
>

many thanks