vat adds up correctly accross spreadsheet but down the columns whe

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
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
 
Boonerball said:
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
Click to expand...

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

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