PRODUCT : QUATTRO PRO NUMBER : 394 VERSION : ALL OS : DOS DATE : March 16, 1991 PAGE : 1/5 TITLE : ROUNDING ERRORS AND THE PERSONAL COMPUTER Rounding errors are an inherent limitation of the PC and the way it stores real numbers in binary format. Even though you enter a value as 123.456, for example, it may be stored in memory (in a binary form) as 123.455899. At this point, you already have a rounding error. If you use this value in a calculation, it can further compound the rounding error factor. When real numbers are stored in memory, they are translated to a binary format without decimal points. Many fractional values cannot be accurately stored in this binary format. Consider the following examples: 2 4 = 2 1+ 0 3 = 2 2 1 2 = 2 0 1 = 2 -1 1/2 = 2 -2 1/4 = 2 -2 -4 -6 -8 -n 1/3 = 2 +2 +2 +2 +....2 where -n is infinity -3 1/8 = 2 These examples illustrate how different values are represented in binary notation. Any integer value can be represented in a valid binary format. However, some fractions (specifically 1/3) can't be accurately stored in the binary format. In fact, 1/3 can't be accurately displayed in base 10 (.3333333333....). Taking a closer look at the value 1/3, in decimal notation, this value appears as .3333333333 and continues through infinity. We can round this value to .3333333, .33, .3, or possibly .3333334. In any form, rounding has taken place and the accuracy of the value has been compromised. The same general concept holds true in binary format. One thing to keep in mind is that many applications (including Quattro Pro) store values to 15 precision places. Changing the PRODUCT : QUATTRO PRO NUMBER : 394 VERSION : ALL OS : DOS DATE : March 16, 1991 PAGE : 2/5 TITLE : ROUNDING ERRORS AND THE PERSONAL COMPUTER format of a cell entry (ie. (F2)-Fixed with two decimal places) does not change the actual value as it is stored in the cell. Again, an example: if cell A1 contains the value 123.456, the computer may store this value as 123.455899. Changing the display format to F2 will display the value in the cell as 123.46. However, the actual value has not changed, only the appearance. EXAMPLE 1 This example assumes the following information is stored in the spreadsheet: 86978.13 <- Derived from a formula 93115.70 <- Derived from a formula 180093.82 <- The sum of the first formulas At first glance, the calculation looks incorrect; or is it? Let's look at some possible examples of how the total (180093.82) may have been calculated. The cell containing the value 86978.13 only displays the first two decimal places of the resulting formula. The actual value resulting from this formula could range from 86978.125 to 86978.134 (this is a simplified example since we are not taking into account the additional places of precision). The actual value resulting from the second formula could range from 93115.695 to 93115.704. With these ranges of values, we can compute different results: 1st Value + 2nd Value = Result(?) F2 Format 86978.125 93115.695 180093.820 180093.82 86978.126 93115.696 180093.822 180093.82 86978.127 93115.697 180093.824 180093.82 86978.128 93115.698 180093.826 180093.83 86978.129 93115.699 180093.828 180093.83 86978.130 93115.700 180093.830 180093.83 86978.131 93115.701 180093.832 180093.83 86978.132 93115.702 180093.834 180093.83 86978.133 93115.703 180093.836 180093.84 PRODUCT : QUATTRO PRO NUMBER : 394 VERSION : ALL OS : DOS DATE : March 16, 1991 PAGE : 3/5 TITLE : ROUNDING ERRORS AND THE PERSONAL COMPUTER 86978.134 93115.704 180093.838 180093.84 Again, this example is over simplified by the fact that we have used the upper and lower limits consistently through the equations. Any combination of the 1st Value and 2nd Value can actually be used to reach the Result. To achieve accurate results for this example, the formula should be written as: @ROUND(@ROUND(86978.13,2)+@ROUND(93115.7,2),2) This formula rounds each value (which was derived from a formula), as well as the resulting value to two decimal places. The result is 180093.83. EXAMPLE 2 This example presents the procedure in Example 1 in a more technical form. Consider the following C program: #include void main(void) { float a, b, c; a = 86978.13; b = 93115.70; c = a + b; printf("%15.10f %15.10f %15.10f\n",a,b,c); } As you can see, the values in this example are hard-coded into the program. However, when this program is compiled and executed, the resulting values are as follows: a: 86978.1328125000 b: 93115.7031250000 c: 180093.8437500000 PRODUCT : QUATTRO PRO NUMBER : 394 VERSION : ALL OS : DOS DATE : March 16, 1991 PAGE : 4/5 TITLE : ROUNDING ERRORS AND THE PERSONAL COMPUTER It is clear that the values assigned to variables a and b have been altered when translated to binary format and placed into memory. This variation is further carried to the result, c. Visually, the results for variable c should be 180093.8359375000 (assuming you have accepted the rounding errors for variables a and b). However, the rounding error has compounded to provide further discrepancies. Don't let this information cast doubt over the ability of a computer. There are different ways you can obtain accurate results. Most programs that perform mathematical calculations support a floating-point emulator. This is a program routine which attempts to emulate a coprocessor. Another method is through a math coprocessor. Coprocessors are the best and fastest means for handling the manipulation of floating point (real) numbers, since the calculations are performed internally. Quattro Pro provides a floating-point emulation routine and support for a math coprocessor chip. It also provides the @ROUND function which lets you round all values to a given degree of precision. In your example, if you use the command @ROUND(your formula,2), where your formula is the formula currently in the cells, you will achieve the desired results. When should you use @ROUND in your formulas? Whenever you are performing any type of calculation on real numbers where precision is of the utmost importance. Again, do not let this information sway your faith in the abilities of a computer or any other piece of hardware/software. This is simply a limitation based on the architecture of the computer industry. Software developers throughout the industry are aware of this limitation and do their best to work with it, not against it. Testing for Equality PRODUCT : QUATTRO PRO NUMBER : 394 VERSION : ALL OS : DOS DATE : March 16, 1991 PAGE : 5/5 TITLE : ROUNDING ERRORS AND THE PERSONAL COMPUTER When testing values for equality, use the @ROUND function to alleviate any chances or rounding errors which can provide incorrect results. For example, if cell A1 and cell A2 contain formulas which return the same result, testing for A1=A2 may return false due to rounding errors. @IF(A1=A2,"Equal","Not Equal") This formula may not always return "True" as expected. However, rewriting the formula as: @IF(@ROUND(A1,2)=@ROUND(A2,2),"True","False") will provide a more reliable result. When using the first format, the values may appear equal in the cells. However, subtracting the cell values (eg. +A1-A2) may return a very small difference (eg. .00001). This will cause the @IF statement to return "False." This is why the @ROUND function should be used.