incorrect calculations

T

tnolen

I have created a worksheet with 4 columns...Price per visit, Tax rate, Tax
amount and Total amount. I have set up the formulas to calculate Tax Amount
and Total Amount. Those columns are formatted for currency. When I total the
tax amount column, it come up 2 cents off. Wouldn't the summing of that
column only add by the 2 decimals that it is displaying?
 
B

Bernard Liengme

When you round to 2 decimal for currency to compute the tax you introduce
rounding errors.
price tax @ 8% rounded to 2 places
$13.55 1.0840 1.08
$54.89 4.3912 4.39
___________________________
$68.44 5.4752 add two values $5.47
Total tax using non-rounded values is 5.4752 ($5.48), but total using
separately rounded values is $5.47

See http://mcgimpsey.com/excel/pennyoff.html
best wishes
 
M

MyVeryOwnSelf

I have created a worksheet with 4 columns...Price per visit, Tax rate,
Tax amount and Total amount. I have set up the formulas to calculate
Tax Amount and Total Amount. Those columns are formatted for currency.
When I total the tax amount column, it come up 2 cents off. Wouldn't
the summing of that column only add by the 2 decimals that it is
displaying?

Even if only two decimal places are showing in a cell, the underlying un-
rounded values are used when a formula elsewhere refers to the cell.

After experiencing a lot of annoyance like you have experienced, I decided
to always ROUND() intermediate values in financial calculations like this,
so the underlying value is exactly the displayed value. It saves me time in
the long run.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top