Rounding Errors Occur in Totals of Displayed Amounts PSS ID Number: Q51853 Article last modified on 03-28-1990 PSS database name: D_MPlan

3.00 3.01 3.02 3.04 4.00 4.01 4.20

MS-DOS

Summary:

Multiplan Versions 3.x and 4.x calculate precise numbers without rounding intermediate results to match what is displayed on the worksheet. The inconsistencies is due to the use of rounded intermediate results displayed in the cells of the spreadsheet.

If rounded numbers are to be used in calculations, the ROUND() function can be applied to the formula containing intermediate results.

More Information:

For example, if two cells contain the number 5.555 and these cells are totaled, the sum will be 11.110. If the cells are formatted to show only two decimal places, Multiplan will display the correct answer of 11.11; however, the two cells containing 5.555 will now display 5.56 instead of 5.555. If you do not examine the exact value of these cells, it appears that the answer using the SUM() function should be 11.12 instead of the correct answer of 11.11. Multiplan does not assume that you want to use rounded numbers in calculations simply because of a change in formatting. Multiplan displays the correct results despite appearances.

In many circumstances, however, you might prefer to total rounded results rather than use the precise calculated amount. This is often the case when adding long columns of dollar amounts. To obtain a total that matches the rounded amounts displayed on the screen, a ROUND() function can be used within each calculated cell.

To continue the example above, let’s assume the following equation generates the number 5.555:

R[-2]C/R[-1]C

As an example, the above formula might be used to compensate an employee for mileage expense on a personal automobile. It would be preferable to total a rounded value of $5.56 on an expense account. To create a value of 5.560 instead of 5.555, use the following formula:

ROUND( R[-2]C/R[-1]C , 2 )

Notice that “R[-2]C/R[-1]C” has been placed within a ROUND() function and that the total is rounded to the nearest two decimal places, which is the same as the nearest cent.

The Edit command is an easy way to modify existing formulas. The F9 and F10 keys can be used to position the cursor within a formula after the Edit command has been selected. Once the formula is corrected, press the ENTER key to save this change.

Under some circumstances it is preferable to truncate numbers rather than round them. Please note that the INT() function can be used to truncate numbers. If the following function is used, the result is $5.00 instead of $5.56:

INT( R[-2]C/R[-1]C )

The result is 5.000 because all digits to the right of the decimal point are ignored when the integer function is applied. The INT() function can be used to create the same result as the ROUND() function in the example above if the following formula is used:

.01 * INT( ( 100 * R[-2]C/R[-1]C ) + .5 )

This formula is presented here to illustrate how rounding is performed. Remember that formulas are always evaluated from the inside out in the following manner:

- “R[-2]C/R[-1]C” is multiplied by 100 to move the decimal place to the right two digits.
- The number .5 is then added to cause units to be increased whenever there are fractional amounts .5 and greater.
- Digits to the right of the current decimal position are then eliminated when the INT() function is applied.
- This result is multiplied by .01 to shift the decimal place back to the left two places. This is the standard method used to round numbers in science and business.

Copyright Microsoft Corporation 1990.