Working out 15% (VAT) in Excel. - Corsa Sport - for Vauxhall and Opel Corsa B, Corsa C and Corsa D

Not logged in [Login - Register]

You Are Not Registered Or Not Logged In

Corsa Sport » Message Board » Off Day » Geek Day » Working out 15% (VAT) in Excel.

New Topic

New Poll

Subscribe | Add to Favourites

You are not logged in and may not post or reply to messages. Please log in or create a new account or mail us about fixing an existing one - register@corsasport.co.uk

There are also many more features available when you are logged in such as private messages, buddy list, location services, post search and more.

deano87

Member

Registered: 21st Oct 06
Location: Bedfordshire Drives: Ford Fiesta
User status: Offline

10th Feb 09 at 11:50

Am I right in thinking the formula would be:

=CELL*15%

?

That is how I work out percentage on a calculator, i.e. 15% VAT on £50 would be 50x15% = £7.50 VAT.

However all the invoices my mum is working through the VAT is different. I then worked out 1% of a figure (easy enough) multiplied it by 15 and it equalled the VAT shown on the invoice, not the VAT as shown in Excel using the above formula.

Totally confused.

Cosmo

Member

Registered: 29th Mar 01
Location: Im the real one!
User status: Offline

10th Feb 09 at 11:56

if it was me I'd be working it out using =SUM(cellwhatever/100)*15

VegasPhil

Premium Member

Registered: 16th Jan 05
Location: Fareham, Hants Drives: Octavia VRS
User status: Offline

10th Feb 09 at 12:05

=CELL*1.15 to give amount inc VAT

Or

=CELL*0.15 to give amount of VAT

But your way would work yeh...

Corsa 2.0 16v Vegas - Sold

Fro

Member

Registered: 20th Jun 06
Location: Rainham, Essex Drives: A3 2.0TDi Sport
User status: Offline

10th Feb 09 at 12:05

Doing a VAT recon at work, and I've used formula's like Cosmopolitan.

deano87

Member

Registered: 21st Oct 06
Location: Bedfordshire Drives: Ford Fiesta
User status: Offline

10th Feb 09 at 12:06

Hmmm ok. Thanks

Tommy L

Member

Registered: 21st Aug 06
Location: Northampton Drives: Audi wagon
User status: Offline

10th Feb 09 at 12:07

£50 is the net figure?

And you want a Net, VAT & Gross figure?

deano87

Member

Registered: 21st Oct 06
Location: Bedfordshire Drives: Ford Fiesta
User status: Offline

10th Feb 09 at 12:10

£50 is the amount inc VAT.

In Excel, using the formulas:
=SUM(C23/100)*15
=C23*0.15
=C23*15%
The VAT is £7.50.

But according to the invoice/receipt, it's £6.52.

Fro

Member

Registered: 20th Jun 06
Location: Rainham, Essex Drives: A3 2.0TDi Sport
User status: Offline

10th Feb 09 at 12:11

You'd have to divide by 115 then x 100 to find the net. Or 15 to find the VAT.
As the gross is technically 115%.

[Edited on 10-02-2009 by fro-dizzle]

Tommy L

Member

Registered: 21st Aug 06
Location: Northampton Drives: Audi wagon
User status: Offline

10th Feb 09 at 12:11

thats correct deano. 15% of £50 is 6.52

[Edited on 10-02-2009 by corsa_tomtom]

deano87

Member

Registered: 21st Oct 06
Location: Bedfordshire Drives: Ford Fiesta
User status: Offline

10th Feb 09 at 12:14

So what is the formula I need to use?

Column 1 = Inc VAT.
Column 2 = VAT
Column 3 = Exc VAT.

Column 2 is what I need the formula for, as Column 3 is a simple Column 1 - Column 2.

Tommy L

Member

Registered: 21st Aug 06
Location: Northampton Drives: Audi wagon
User status: Offline

10th Feb 09 at 12:15

column 2 will be column 1 less column3

Fro

Member

Registered: 20th Jun 06
Location: Rainham, Essex Drives: A3 2.0TDi Sport
User status: Offline

10th Feb 09 at 12:16

quote:
Originally posted by deano87
So what is the formula I need to use?

Column 1 = Inc VAT.
Column 2 = VAT - =(column 1/115)*15
Column 3 = Exc VAT.

Column 2 is what I need the formula for, as Column 3 is a simple Column 1 - Column 2.

deano87

Member

Registered: 21st Oct 06
Location: Bedfordshire Drives: Ford Fiesta
User status: Offline

10th Feb 09 at 12:17

quote:
Originally posted by corsa_tomtom
column 2 will be column 1 less column3

It's doing everything automatically.

So mum is only punching in the figure for Column 1. The VAT and amount minus VAT is then put in Column 2 and 3 automatically. . .

deano87

Member

Registered: 21st Oct 06
Location: Bedfordshire Drives: Ford Fiesta
User status: Offline

10th Feb 09 at 12:17

quote:
Originally posted by fro-dizzle

quote:
Originally posted by deano87
So what is the formula I need to use?

Column 1 = Inc VAT.
Column 2 = VAT - =(column 1/115)*15
Column 3 = Exc VAT.

Column 2 is what I need the formula for, as Column 3 is a simple Column 1 - Column 2.

Tommy L

Member

Registered: 21st Aug 06
Location: Northampton Drives: Audi wagon
User status: Offline

10th Feb 09 at 12:19

Column 1 = No formula, Gross figure is entered here
Column 2 = Column 1 (A1) - Column 3 (C1)
Column 3 = column 1 (A1) / 1.15

thats how i do it

[Edited on 10-02-2009 by corsa_tomtom]

[Edited on 10-02-2009 by corsa_tomtom]

Cosmo

Member

Registered: 29th Mar 01
Location: Im the real one!
User status: Offline

10th Feb 09 at 12:24

quote:
Originally posted by fro-dizzle
You'd have to divide by 115 then x 100 to find the net. Or 15 to find the VAT.
As the gross is technically 115%.

[Edited on 10-02-2009 by fro-dizzle]

Or 3/23 as is normally used (just it worked down).

deano87

Member

Registered: 21st Oct 06
Location: Bedfordshire Drives: Ford Fiesta
User status: Offline

10th Feb 09 at 17:12

quote:
Originally posted by corsa_tomtom
thats correct deano. 15% of £50 is 6.52

I was thinking about this when I was on my way to Uni.

And worked out in my head that 15% of 50 is £7.50.

10% of £50.00 = £5.00
which means
5% = half of 10% = £2.50.

10% + 5% = £7.50.

Or is my school days talking rubbish!?

[Edited on 10-02-2009 by deano87]

M2RTY

Member

Registered: 25th May 01
User status: Offline

10th Feb 09 at 17:23

2 different calculations depending on if VAT was already added or not:

50 quid total, how much of it is VAT?
so,
50-((50/115)*100)
=6.52 of the 50 is VAT

50 quid without VAT, how much with VAT added on?
so,
50*0.15
=7.50 added to 50 makes total with VAT

Tommy L

Member

Registered: 21st Aug 06
Location: Northampton Drives: Audi wagon
User status: Offline

10th Feb 09 at 17:47

quote:
Originally posted by deano87

quote:
Originally posted by corsa_tomtom
thats correct deano. 15% of £50 is 6.52

I was thinking about this when I was on my way to Uni.

And worked out in my head that 15% of 50 is £7.50.

10% of £50.00 = £5.00
which means
5% = half of 10% = £2.50.

10% + 5% = £7.50.

Or is my school days talking rubbish!?

[Edited on 10-02-2009 by deano87]