Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
rounding error
My database in Access 2003 has a form which calculates Quantity by the Price
in the footer which is then multiplied by a Tax rate of 12.5% and the two added to get a Total . I get the following figures in the form Subtotal: 96,738.67 Tax: 12,092.33 TOTAL: 108,831.01. The correct answer should be 108,831.00. I have tried various formats (currency, standard, fixed with rounding Auto & 2) but am not able to correct for that small difference. Any solutions |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
rounding error
"pkeegs" wrote:
My database in Access 2003 has a form which calculates Quantity by the Price in the footer which is then multiplied by a Tax rate of 12.5% and the two added to get a Total . I get the following figures in the form Subtotal: 96,738.67 Tax: 12,092.33 TOTAL: 108,831.01. The correct answer should be 108,831.00. If you are asking about calculations performed in Excel (this is an Excel newsgroup, not an Access newsgroup), the simple answer is: all calculations that should result in dollars-and-cents should be rounded to 2 decimal places. There are two ways to do that: I prefer explicitly using ROUND in the formulas. For example, B1: =ROUND(SUM(A1:A100),2) B2: =ROUND(B1*12.5%,2) B3: =ROUND(B1+B2,2) For example, note that 96738.67*12.5% is actually 12092.33375 if you do the compuation manually or with a calculator. If you carry the extra fractional digits through other calculations, eventually you will see noticable "errors" in expected results. But in your example, note that 96738.67 + 12092.33375 would still apear to be 108831.00 when displayed to 2 decimal places. So we can infer that 96738.67 is not exactly that either; in fact, it is between 96738.67125 and just less than 96738.68125. There are several ways that might happen; but very likely, it is because some other intermedicate computation results in more than 2 fractional digits, just as multiplying by 12.5% did. PS: The alternative to explicit use of ROUND is to use the option Precision As Displayed under Tools Options Calculation (in Excel 2003). I deprecate the use of that feature because it can be very dangerous if you are not careful. If you choose to experiment with PAD, be sure to make a backup copy of the Excel file before you set the option. PAD will implicitly round to 2 decimal places only if you use a numeric format (Number, Accounting, Currency, etc) with 2 decimal places. In particular, it has not impact on the results in cells formatted as General. I have tried various formats (currency, standard, fixed with rounding Auto & 2 Unless you use Precision As Displayed (see above), formats with 2 decimal places only change the appearance of values, not the actual value in the cell. ----- original message ----- "pkeegs" wrote: My database in Access 2003 has a form which calculates Quantity by the Price in the footer which is then multiplied by a Tax rate of 12.5% and the two added to get a Total . I get the following figures in the form Subtotal: 96,738.67 Tax: 12,092.33 TOTAL: 108,831.01. The correct answer should be 108,831.00. I have tried various formats (currency, standard, fixed with rounding Auto & 2) but am not able to correct for that small difference. Any solutions |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
rounding error
Errata....
I wrote: So we can infer that 96738.67 is not exactly that either; in fact, it is between 96738.67125 and just less than 96738.68125. It is between about 96738.6711111111 and about 96738.6749999999 if the subtotal plus tax displays as 108,831.01, where subtotal displays as 96,738.67 and tax displayes as 12,092.33. Tax (12.5% of subtotal) is actually between about 12092.3338888889 and about 12092.3343750000 respectively. Their sum is actually between about 108831.005000000 and about 108831.009375000. There are several ways that might happen; but very likely, it is because some other intermedicate computation results in more than 2 fractional digits, just as multiplying by 12.5% did. Another possible source of the "error" in the subtotal (i.e unexpected fractional digits) is due to the way that Excel (and Access, presumably) store numbers and perform arithmetic. It is caused binary floating point. Consequently, most numbers with fractional digits cannot be represented exactly. Summing a large quantity of such numbers might introduce a significant "error". (I was trying to avoid the topic.) ----- original message ----- "Joe User" <joeu2004 wrote in message ... "pkeegs" wrote: My database in Access 2003 has a form which calculates Quantity by the Price in the footer which is then multiplied by a Tax rate of 12.5% and the two added to get a Total . I get the following figures in the form Subtotal: 96,738.67 Tax: 12,092.33 TOTAL: 108,831.01. The correct answer should be 108,831.00. If you are asking about calculations performed in Excel (this is an Excel newsgroup, not an Access newsgroup), the simple answer is: all calculations that should result in dollars-and-cents should be rounded to 2 decimal places. There are two ways to do that: I prefer explicitly using ROUND in the formulas. For example, B1: =ROUND(SUM(A1:A100),2) B2: =ROUND(B1*12.5%,2) B3: =ROUND(B1+B2,2) For example, note that 96738.67*12.5% is actually 12092.33375 if you do the compuation manually or with a calculator. If you carry the extra fractional digits through other calculations, eventually you will see noticable "errors" in expected results. But in your example, note that 96738.67 + 12092.33375 would still apear to be 108831.00 when displayed to 2 decimal places. So we can infer that 96738.67 is not exactly that either; in fact, it is between 96738.67125 and just less than 96738.68125. There are several ways that might happen; but very likely, it is because some other intermedicate computation results in more than 2 fractional digits, just as multiplying by 12.5% did. PS: The alternative to explicit use of ROUND is to use the option Precision As Displayed under Tools Options Calculation (in Excel 2003). I deprecate the use of that feature because it can be very dangerous if you are not careful. If you choose to experiment with PAD, be sure to make a backup copy of the Excel file before you set the option. PAD will implicitly round to 2 decimal places only if you use a numeric format (Number, Accounting, Currency, etc) with 2 decimal places. In particular, it has not impact on the results in cells formatted as General. I have tried various formats (currency, standard, fixed with rounding Auto & 2 Unless you use Precision As Displayed (see above), formats with 2 decimal places only change the appearance of values, not the actual value in the cell. ----- original message ----- "pkeegs" wrote: My database in Access 2003 has a form which calculates Quantity by the Price in the footer which is then multiplied by a Tax rate of 12.5% and the two added to get a Total . I get the following figures in the form Subtotal: 96,738.67 Tax: 12,092.33 TOTAL: 108,831.01. The correct answer should be 108,831.00. I have tried various formats (currency, standard, fixed with rounding Auto & 2) but am not able to correct for that small difference. Any solutions |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
My rounding error in a simple IF | Excel Worksheet Functions | |||
rounding error? | Excel Discussion (Misc queries) | |||
Rounding error is conditional on brackets | Excel Discussion (Misc queries) | |||
Have Excel display an error rather than rounding # to fit column | Setting up and Configuration of Excel | |||
Percentage rounding error in charts | Excel Discussion (Misc queries) |