Excel - working out net and vat?

[louise1989]

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]

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]

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]

Originally Posted by louise1989

...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]

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]

Originally Posted by TheNorm

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]

Originally Posted by louise1989

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]

Don't forget to adjust the calculations to account for the new VAT rate of 20% though.

[Tezcatlipoca]

Originally Posted by progers

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]

Originally Posted by TheNorm

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

Originally Posted by Lord Nikon

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]

Originally Posted by louise1989

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]

Originally Posted by haydnwalker

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]

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]

Originally Posted by TheNorm

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 !