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?

:tu:

Re: Excel: Calculating VAT
 

Net = Total / 1.175

VAT = Total - Net.

Re: Excel: Calculating VAT
 

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

Re: Excel: Calculating VAT
 

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! :erm:

Re: Excel: Calculating VAT
 

Nope - £24.75 + VAT = £29.08

(£24.75 x 1.175)

Re: Excel: Calculating VAT
 

£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%.

Re: Excel: Calculating VAT
 

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

Now confused!!!!!

Re: Excel: Calculating VAT
 

Taking 17.5% from 30 is not the same thing.

Re: Excel: Calculating VAT
 

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

Re: Excel: Calculating VAT
 

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. :)

Re: Excel: Calculating VAT
 

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! :disturbd:

Re: Excel: Calculating VAT
 

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.

Re: Excel: Calculating VAT
 

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

Re: Excel: Calculating VAT
 

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???

Re: Excel: Calculating VAT
 

Correct :)

Re: Excel: Calculating VAT
 

Based on the conversation above, and the fact that you've been happy with the results generated, then I'd say yes.

That said, I'd do the calculations differently - that's just me though.

i'm going to tidy the posts above up I think..... :erm:

Re: Excel: Calculating VAT
 

Thought so - thanks for confirming!

Re: Excel: Calculating VAT
 

The 30 is 117.5% of the net not 82.5% 7/47th as a fraction.

If you want to do it that way it is 30-14.89361702127659574468085106383% (or 40/47ths)

Now it is 15% the vat fraction is 3/23rds

---------- Post added at 13:20 ---------- Previous post was at 13:17 ----------

What you really should do for clarity is put a line in it that says VAT RATE and then type the % in the box next to it and then refer all the VAT calcs to that number) Say it is in A27 that woudl be =sum(E19/(1+$A$27))
It's bad spreadsheet practice to put variables in as fixed numbers in multiple cells.

Re: Excel: Calculating VAT
 

What a waste of time - I mean - come on - how often does the VAT rate change.......


:erm:



Errrrrr - right - I see - will do that - good call!