Works: Rounding Error in Spreadsheet
PSS ID Number: Q40954 Article last modified on 06-12-1996
1.00 1.05 2.00
MS-DOS
| The information in this article applies to: |
| - Microsoft Works for MS-DOS, versions 1.x and 2.0 |
Summary:
In the Spreadsheet module of Works versions 1.x and 2.0, rounded calculations are not accurate under certain conditions.
Workaround 1
Perform the calculations using integers, that is, numbers with nothing after the decimal point.
Workaround 2
Add a very small number to the ROUND formula. This small amount compensates for the rounding error (usually occurring in the 16th or 17th decimal place). For example, to correct the rounding error in the first example, under “More Information,” you would add the number .000000001 to the formula ROUND(AVG(A1:A8),3). The resulting formula would be ROUND(AVE(A1:A8)+ .000000001,3).
Microsoft has confirmed this to be a problem in Works versions 1.x and 2.0. This problem was corrected in Works version 3.0.
More Information:
This problem is caused by a binary rounding error (the binary number format is the way that computers store numbers). Some numbers, most notably 0.1, are repeating numbers in the binary number format. When these repeating numbers are added together, the results are not always as expected. For example:
Add 1/3 + 1/3 + 1/3. We know the sum is 1. When you add this on an ordinary calculator, you get .3333 + .3333 + .3333 = .9999. Because the number 1/3 is a repeating number, it does not add up correctly.
If you created a spreadsheet that resembled the following:
| A |
1| 3.21 | 2| 3.25 | 3| 3.12 | 4| 3.13 | 5| 3.18 | 6| 3.26 | 7| 3.38 | 8| 3.25 |
and took the average of all eight numbers using the formula AVG(A1:A8), the average is 3.2225. If you round the number to three decimal places using the formula ROUND(AVG(A1:A8),3), the average should be 3.223; however, Works versions 1.x and 2.0 produce 3.222 as the result of this formula.
Another example can be shown using the formula ROUND(20675*1.4+0.5,0). The correct result of this formula is 28946, but Works versions 1.x and 2.0 incorrectly round this number to 28945.
KBCategory: kbbuglist kbfixlist KBSubcategory: dworkskb
Additional reference words: 1.00 1.05 2.00 one-third plus
Copyright Microsoft Corporation 1996.
