Cable Forum
Page 1 of 2 1 2
Show 100 post(s) from this thread on one page

Cable Forum (https://www.cableforum.uk/board/index.php)
-   General IT Discussion (https://www.cableforum.uk/board/forumdisplay.php?f=19)
-   -   Excel: Calculating VAT (https://www.cableforum.uk/board/showthread.php?t=45660)

LSainsbury 07-04-2006 10:14
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:

Paul 07-04-2006 10:18
Re: Excel: Calculating VAT
 
Net = Total / 1.175

VAT = Total - Net.

yesman 07-04-2006 10:21
Re: Excel: Calculating VAT
 
Quote:
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?

:tu:
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

LSainsbury 07-04-2006 10:28
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:

BillyDrago 07-04-2006 10:38
Re: Excel: Calculating VAT
 
Nope - £24.75 + VAT = £29.08

(£24.75 x 1.175)

Paul 07-04-2006 10:39
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%.

LSainsbury 07-04-2006 10:56
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!!!!!

Paul 07-04-2006 10:58
Re: Excel: Calculating VAT
 
Taking 17.5% from 30 is not the same thing.

LSainsbury 07-04-2006 11:02
Re: Excel: Calculating VAT
 
Quote:
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!!!:Yikes:

Paul 07-04-2006 11:05
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. :)

LSainsbury 07-04-2006 11:18
Re: Excel: Calculating VAT
 
Quote:
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! :disturbd:

homealone 07-04-2006 11:29
Re: Excel: Calculating VAT
 
Quote:
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! :disturbd:
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.

LSainsbury 07-04-2006 11:37
Re: Excel: Calculating VAT
 
Quote:
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!!!!

LSainsbury 28-01-2009 17:04
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???

Acathla 28-01-2009 17:12
Re: Excel: Calculating VAT
 
Correct :)


All times are GMT +1. The time now is 05:26.
Page 1 of 2 1 2
Show 100 post(s) from this thread on one page

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
All Posts and Content are © Cable Forum