Excel & V.A.T

  ste_bla 11:13 21 Oct 04
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 04

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 04

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 04

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

  ste_bla 15:27 21 Oct 04

Cheers mate

  Sparrowhawk 18:35 21 Oct 04

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 04

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 04

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 04

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.

What is Amazon Go and will it come to the UK? The store without checkouts or queues

1995-2015: How technology has changed the world in 20 years

Hands-on with the Star Wars fighting drones you can fly yourself

15 macOS Sierra tips | How to use macOS Sierra: Secret tricks and best new features in Apple's new…