Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
why are the calculations incorrect in my spreadsheet? | Excel Worksheet Functions | |||
time sum incorrect | Excel Worksheet Functions | |||
Incorrect Am and Pm | Excel Discussion (Misc queries) | |||
Incorrect Sum | Excel Discussion (Misc queries) | |||
Incorrect Sum | Excel Discussion (Misc queries) |