Article ID: 107462
Article Last Modified on 11/15/2004
APPLIES TO
- Microsoft Works 2.0 Standard Edition
- Microsoft Works 2.0a
- Microsoft Works 3.0 Standard Edition
- Microsoft Works 4.5 Standard Edition
- Microsoft Works 4.5a
- Microsoft Works 4.0 Standard Edition
- Microsoft Works 4.0a
This article was previously published under Q107462
SYMPTOMS
Spreadsheet formulas and functions in Microsoft Works for Windows may return incorrect results in certain cases due to a binary rounding error. Some decimal numbers, most notably 0.1, are repeating binary numbers. When repeating numbers are added together, the result may not always be accurate.
The error will usually occur in the sixteenth or seventeenth decimal place and can be ignored in most instances.
For example, the returned value when 100.1-100-0.1 is calculated is given in scientific notation as -5.69E-15. This number is -5.69*10^-15 = -0.00000000000000569. While this number is not zero, it is close enough for most computational purposes.
The Windows Calculator and Microsoft Works for MS-DOS return similar calculation errors.
RESOLUTION
This error can be corrected up to 13 significant digits (14 would not correct the error in all cases) using the ROUND function.
The ROUND function has the syntax:
=ROUND(x,NumberOfPlaces)
For each formula or function that is returning an incorrect result, place the ROUND function in the same cell with the original formula or function as the "x" parameter and a number from 0 to 13 as the "NumberOfPlaces" parameter to indicate the number of significant digits after the decimal desired.
The following examples demonstrate some common calculation errors that may occur and the accompanying ROUND function that will correct the error in each case:
Formula Result Actual ROUND()
------- ------ ------ -------
=0.145*100-29/2 -1.77636E-15 0 =ROUND(0.145*100-29/2,13)
=MOD(0.28*100,2) 3.55271E-15 0 =ROUND(MOD(0.28*100,2),13)
=100.84-100-0.84 3.44169E-15 0 =ROUND(100.84-100-0.84,13)
=100.1-100-0.1 -5.68989E-15 0 =ROUND(100.1-100-0.1,13)
These errors occur when all of the bits in a binary number cannot be used in a calculation (computers use a binary number format to store numbers internally).
Additional query words: w_works kbhowto ss fmt func form 2.00 2.00a 3.00 4.00 4.00a 4.50 4.50a
Keywords: kberrmsg kbprb KB107462
