Works: Calculation Errors in Spreadsheet Functions (107462)

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).