Cable Forum
Page 2 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 - working out net and vat? (https://www.cableforum.uk/board/showthread.php?t=33673747)

louise1989 09-01-2011 00:57
Re: Excel - working out net and vat ???
 
I used to be good at excel back in the day but for some reason it will not sink back in I hope its just lack of sleep as working out the vat and net on excel will be soo much quicker than writing it all down and using a calculator ! !

rogerdraig 09-01-2011 01:04
Re: Excel - working out net and vat ???
 
if your carrying on i would put in G2 =SUM(C2)+D2

that will confirm the 2 added together do add up to what you started with

louise1989 09-01-2011 14:00
Re: Excel - working out net and vat ???
 
woohooo managed to get the vat to work out right after much fidderling about right next question can i make the net and vat automatically round up to the nearest penny ??

TheNorm 09-01-2011 14:20
Re: Excel - working out net and vat ???
 
Quote:
Originally Posted by louise1989 (Post 35149707)
...can i make the net and vat automatically round up to the nearest penny ??
Try:

ROUNDUP(number,num_digits)

Replace number by your existing formula, and num_digits by 2

So, if cell G2 is currently:

=SUM(E2/117.5)*100

You add the ROUNDUP function as follows:

=ROUNDUP(SUM(E2/117.5)*100, 2)

progers 09-01-2011 15:14
Re: Excel - working out net and vat?
 
A price including VAT (before it went up) is equivalent to 1.175 times the nett price so the nett price is the price with VAT divided by 1.175 and the VAT is the price with VAT minus the nett price

louise1989 09-01-2011 15:22
Re: Excel - working out net and vat ???
 
Quote:
Originally Posted by TheNorm (Post 35149722)
Try:

ROUNDUP(number,num_digits)

Replace number by your existing formula, and num_digits by 2

So, if cell G2 is currently:

=SUM(E2/117.5)*100

You add the ROUNDUP function as follows:

=ROUNDUP(SUM(E2/117.5)*100, 2)
Sorry can u say that again but dumb it down a bit as im thick when it comes to excel and formulars !

TheNorm 09-01-2011 15:25
Re: Excel - working out net and vat ???
 
Quote:
Originally Posted by louise1989 (Post 35149768)
Sorry can u say that again...!
1. Find the cell you want to round up.

2. If the formula in that cell is

=XXX

Then change this to

=ROUNDUP(XXX,2)

3. You are finished!

Lord Nikon 09-01-2011 15:58
Re: Excel - working out net and vat?
 
Don't forget to adjust the calculations to account for the new VAT rate of 20% though.

Tezcatlipoca 09-01-2011 16:11
Re: Excel - working out net and vat?
 
Quote:
Originally Posted by progers (Post 35149763)
A price including VAT (before it went up) is equivalent to 1.175 times the nett price so the nett price is the price with VAT divided by 1.175 and the VAT is the price with VAT minus the nett price
http://www.cableforum.co.uk/board/35149515-post9.html

http://www.cableforum.co.uk/board/35149518-post11.html

;) :)

louise1989 09-01-2011 17:34
Re: Excel - working out net and vat ???
 
Quote:
Originally Posted by TheNorm (Post 35149772)
1. Find the cell you want to round up.

2. If the formula in that cell is

=XXX

Then change this to

=ROUNDUP(XXX,2)

3. You are finished!
Much better I will try that when I get home thanks !

---------- Post added at 17:34 ---------- Previous post was at 17:32 ----------

Quote:
Originally Posted by Lord Nikon (Post 35149797)
Don't forget to adjust the calculations to account for the new VAT rate of 20% though.
Yes I know im doing last quater at the moment so I needed the 17.5% and when I do this quater I just need to change the formular to the new tax rate correct?

haydnwalker 09-01-2011 18:59
Re: Excel - working out net and vat ???
 
Quote:
Originally Posted by louise1989 (Post 35149707)
woohooo managed to get the vat to work out right after much fidderling about right next question can i make the net and vat automatically round up to the nearest penny ??
Right click on the cells you want to round to the nearest penny and choose "format cells" and click on the number tab and change the decimal places to 2 :) that should be it - no calculations necessary.

OR do the same right click etc and set the number format to currency :)

TheNorm 09-01-2011 19:16
Re: Excel - working out net and vat ???
 
Quote:
Originally Posted by haydnwalker (Post 35149894)
Right click on the cells you want to round to the nearest penny and choose "format cells" and click on the number tab and change the decimal places to 2 :) that should be it - no calculations necessary.

OR do the same right click etc and set the number format to currency :)
Are you sure those methods round up?

haydnwalker 09-01-2011 19:36
Re: Excel - working out net and vat?
 
I know the currency format does. I would guess changing to 2DP rounds up/down as necessary but haven't tried it with VAT calcs.

louise1989 09-01-2011 19:49
Re: Excel - working out net and vat ???
 
Quote:
Originally Posted by TheNorm (Post 35149904)
Are you sure those methods round up?
Yes it does work tried that as it was a little bit quicker

Thanks everybody for your help finally got it no more paperwork yay !


All times are GMT +1. The time now is 09:30.
Page 2 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