vat adds up correctly accross spreadsheet but down the columns whe

[Guest]

I have a very basic spread sheet which adds VAT to various cost figures.

I have a total VAT cell below the VAT columns. When adding the columns
together the answer is normally out by 1p less.

Example:

Cost VAT
£317.00 55.48
£317.00 55.48

Total VAT should be £110.96 but Excel calculates at £110.95 The above
calculation may be a bad example but the above occurs when the VAT is rounded
up or down against an individual cost figure. So why is the Total different
if the sum is just the adding up of the VAT individual totals?

Thanks

 

[Alex Delamain]

because 17.5% of 317 is actually 55.475

Excel will display this as 55.48 but when you total the column it wil
actually use 55.47

 

[Carl Jarvis]

Excel is displaying 55.48 as the answer but is really thinking 55.475 is teh
REAL answer.

55.475 and 55.475 = 110.95

You can use ROUND functions to predict and control how excel treats these
numbers.

Hope this helps

 

[Alex Delamain]

Sorry did not complete my reply.

However you might want to look at the following site instead

http://www.mcgimpsey.com/excel/pennyoff.htm

 

[Niek Otten]

In addition to Carl and Alex: consider the use of Tools>Options>Calculation,
"Precision as displayed". Often a good idea for financial spreadsheets, but
read HELP first to understand the implications.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Gordon]

I have a very basic spread sheet which adds VAT to various cost
figures.

I have a total VAT cell below the VAT columns. When adding the columns
together the answer is normally out by 1p less.

Example:

Cost VAT
£317.00 55.48
£317.00 55.48

Total VAT should be £110.96

No it's not. £634 x 17.5% is £110.95

Vat on £317 is £55.475. Twice £55.475 = £110.950