Incorrect Calculation

[Sam Goddard]

Hello,

I have noticed that Excel 2003 does not always calculate figures correctly.

I have been trying to total up an invoice by having a subtotal & a VAT cell
(VAT being the subtotal plus 17.5%). Being as VAT is 17.5%, the figure
calculates is sometimes a half penny.

When it comes to adding these two cells together, if they are £40.02 &
£10.03, I might get a total of 50.06. I assume that this is because,
although the cells are formatted to two decimal places, when excel adds two
cells together, it adds formulae rather than the figures.

Can anybody tell me give me more help & possibly suggest a solution so that
these two cells will add up correctly?

Thanks

Sam

 

[Don Guillett]

Have a look in HELP index for ROUND

 

[Sam Goddard]

I shall do, but how will this help in a way that taking the result to 2
decimal places will not?

Thanks for the suggestion.

Sam

 

[Peo Sjoblom]

See

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

 

[Andy Wiggins]

If a value in an unformatted cell is 1.2468, then formatting it to two
decimal places will make it show 1.25. Formatting a cell does not affect the
underlying figures, so calculations on that cell will use the unformatted
number.

The ROUND function forces the use of a rounded figure. So it you have 1.2468
in cell A1, then a calculation that uses ROUND(A1,2) will use 1.25, and a
calculation using ROUND(A1,1) will use 1.2.

When calculating VAT, the VAT amount is rounded to the nearest penny below.
To do that you can use the ROUNDDOWN function.

 

[Guest]

See:

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


Regards,

Peo Sjoblom