Excel: Calculating VAT

Hello,

Very basic Excel question - I'm sure i've done this before but as it's Friday morning I can't work it out!

I have an expsnses worksheet n Excel.

It has three columns - Net Amount, VAT and Total.

What I want to do is just complete the total column and have Excel work out the Net and VAT amounts...

Any ideas on the simple sum formula?

Net = Total / 1.175

VAT = Total - Net.

Originally Posted by lsainsbury

Hello,

Very basic Excel question - I'm sure i've done this before but as it's Friday morning I can't work it out!

I have an expsnses worksheet n Excel.

It has three columns - Net Amount, VAT and Total.

What I want to do is just complete the total column and have Excel work out the Net and VAT amounts...

Any ideas on the simple sum formula?

The net amount would be the total divided by 1.175, then the VAT would be the net amount multiplied by 17.5%

Edit: beaten to it by Paul

Ummm....

Thanks for the responses...

If I put a total in of £30 - the VAT is £4.47 and the net is £25.53....thats wrong isn't it? Should'nt it be £5.25 and £24.75? - At least thats what calc works it out too!

[BillyDrago]

Nope - £24.75 + VAT = £29.08

(£24.75 x 1.175)

£25.53 is correct.

17.5% of £25.53 = £4.47 (25.53 * 0.175)

The two add up to £30

£24.75 is clearly wrong and would be about 21.2%.

So how comes when in calc you do the calculation of 30-17.5% = Net £24.75 / VAT=£5.25?

Now confused!!!!!

Taking 17.5% from 30 is not the same thing.

Originally Posted by Paul M

Taking 17.5% from 30 is not the same thing.

Is it not? That's how I've always worked it out!!!

Nope.

Imagine if the net was £10, and VAT was 10%, then the total would be £10 + £1 = £11.

However £11 - 10% is £11 - £1.10 = £9.90 - not the same calculation.

Originally Posted by Paul M

Nope.

Imagine if the net was £10, and VAT was 10%, then the total would be £10 + £1 = £11.

However £11 - 10% is £11 - £1.10 = £9.90 - not the same calculation.

I see what you mean! OK - Won't use the % on a calc ever again!

One final thing....how do I copy just the forumla to other cells....paste special formulas dosn't seem to work!

Originally Posted by lsainsbury

One final thing....how do I copy just the forumla to other cells....paste special formulas dosn't seem to work!

if copying vertically, just highlight the cells with the formulas in, then left click on the little black square in the bottom right of the highlighted cells & drag downwards for as far as you want the formulas to apply.

Originally Posted by homealone

if copying vertically, just highlight the cells with the formulas in, then left click on the little black square in the bottom right of the highlighted cells & drag downwards for as far as you want the formulas to apply.

Thats the monkey!! Sorted - cheers!!!!

Bump!

Not used my Overtime sheet for a while - had minimal expenses just been put through the tin, but this month I have a few so back to the Excel sheet.

It currently has this formula to work out VAT:

=SUM(E19/1.175)

I take it that should now read this since the rate has changed:

=SUM(E19/1.150)

Correct???

[Acathla]

Correct