Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default incorrect calculations

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?
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,393
Default incorrect calculations

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"tnolen" wrote in message
...
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?



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,624
Default incorrect calculations

See

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

In article ,
tnolen wrote:

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?

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 213
Default incorrect calculations

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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
why are the calculations incorrect in my spreadsheet? Peepdip Excel Worksheet Functions 1 July 4th 06 01:09 PM
time sum incorrect mtovbin Excel Worksheet Functions 7 June 1st 06 04:18 AM
Incorrect Am and Pm MrBlackForest Excel Discussion (Misc queries) 2 January 26th 06 04:11 AM
Incorrect Sum ashlandpmac Excel Discussion (Misc queries) 1 April 15th 05 01:25 AM
Incorrect Sum AshlandPmac Excel Discussion (Misc queries) 1 April 14th 05 10:41 PM


All times are GMT +1. The time now is 12:42 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"