# Excel & V.A.T

ste_bla 11:13 21 Oct 2004
Locked

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

Cheers

mgmcc 12:17 21 Oct 2004

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

=INT(A1*0.175*100)/100

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

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

Fujitsu Lifebook P727 laptop review

Microsoft Paint set to die after 32 years

Mac power user tips and hidden tricks

Comment désactiver la saisie intuitive et paramétrer votre clavier ?