Excel & V.A.T

  ste_bla 11:13 21 Oct 2004

Doing 2nd year of my Accountancy course and was wondering to make VAT calulations quicker is it possible to get excel to round down only eg if £9.999 then it would be £9.99 because at moment have to do it by calculator and my fingers hurt :(


  mgmcc 12:17 21 Oct 2004

If the main value on which you are calculating VAT is in cell A1, then


This calculates the VAT, multiplies it by 100, drops the decimal places and then divides back by 100. Result, it rounds VAT down

  TonyV 15:24 21 Oct 2004

ste-bla, You can always use the =ROUNDOWN element in the formula. i.e. =ROUNDDOWN(A1,2) Or if you are trying to put the figure in in one go by say adding to cells together, and making the result rounded down use =ROUND(SUM(A1:B1),2). Don't forget to format the cell that has the reult in to be shown at 2 decimal points.

  TonyV 15:26 21 Oct 2004

ste-bla, sorry, missed the DOWN off the second formula!! It should be =ROUNDDOWN(SUM(A1:B1),2)

  ste_bla 15:27 21 Oct 2004

Cheers mate

  Sparrowhawk 18:35 21 Oct 2004

If like me you are not an expert neither at accounting or IT... be a ERP, Business Applications, Financials software consultant (call it as you want).
Trust me, I am one.

  Sparrowhawk 18:43 21 Oct 2004

I am not sure the INT function would be correct in all cases.

I would recommend the ROUNDDOWN function.

But, why not using:
1. 'Right-Click' on cells that need to have 2 decimals
2. Select 'Format Cells'
3. Select 1st Tab 'Number'
4. Select Category 'Number', 'Currency' (adds a symbol), 'Accounting' (adds a symbol, but does not show negative amounts).
5. Select 'Decimal Places = 2'

  wee eddie 19:37 21 Oct 2004

How do you mean - Quicker.

My PC does the calculation in nanoseconds.

Might you need to rephrase the question.

Sparrowhawk is correct. Just format the column to display to 2 decimal places.

By the by. If you have the gross figure and wish to know how much of it is VAT x7/47.

Rounding up or down: If the third decimal place is above 5 (<), Round up. If below 5 (>)Round down

  Friday's Child 21:39 21 Oct 2004

We use the Round function ie if the Net sum is in A1 =Round(A1*0.175,2)

This will round the figure up or down as necessary.

Be careful if you have more than one item to do and are going to have Excel add them together not to just change the visible formatting in the results cells because they will still contain the 3rd and subsequent decimal places which will be added into your total.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

iMac Pro review

Illustrator Charles Williams on how to create magazines and book covers

iMac Pro review

Les meilleures prises CPL (2018)