Rounding Errors In Excel

Rounding Errors
In Microsoft� Excel97�

You can download this article as either a Word97 or RTF file.

This articles applies to all versions of Microsoft Excel For Windows.

This article was written by Chip Pearson, 27-Oct-1998.

� Copyright, 1998, 1999, Charles H. Pearson

Article Summary:

This article describes the reasons why you may experience arithmetic errors in Microsoft Excel97.

Article Contents:

Prerequisites:

This article assumes that you are familiar with the following:

Introduction

There may be times that the value that you see on an Excel worksheet does not equal the value that you believe that it should be. There are generally two possible causes of this problem. The first is that the numbers are not displayed to their full values. The second is a computer design issue. Neither of the two are "bugs" or problems with the design of Microsoft Excel or Windows.

Excel stores numbers differently that you may have them formatted display on the worksheet. Under normal circumstances, Excel stores numeric values as "Double Precision Floating Point" numbers, or "Doubles" for short. These are 8-byte variables that can store numbers accurate to approximately 15 decimal places. You may have only two decimal places displayed on the worksheet, but the underlying value has the full 15 decimal places.

The second problem arises from the fact that a computer, any computer, cannot store most fractional numbers with total accuracy. Computers, in general, use the IEEE (Institute Of Electrical And Electronic Engineers) standard for floating point numbers. This standard provides a way to store fractional numbers in the limited space of an 8-byte number. Of course, for most numbers, some approximation must be made.

This article describes and explains the causes for errors that are due to either of the causes described above: the displayed formatted number and the internal errors associated with floating point numbers.

 Actual And Displayed Values

Under normal circumstances, Excel always stores and manipulates numbers as 8-byte "Double Precision Floating Point" numbers, or "Doubles". Excel's internal storage of the number is not affected by the way that you may choose to format a number for display. For example, if a cell contains the formula =1/3, Excel always treats this value as 0.3333…, regardless of how many decimal places you choose to display on the worksheet. Even if you choose to display the value as simple "0.3", Excel still retains the complete number as the value of the cell.

This can cause situations in which it may appear that Excel is making an error in calculation, when it is really not. For example, suppose we have the formula =1/3 in each of the three cells A1:A3. Formatting these cell for one decimal point would show "0.3" in each cell. Adding these three cells together with the SUM function will give the result 1.0. But 0.3 + 0.3 + 0.3 equals 0.9 not 1.0, right? The result would appear to be incorrect.

Of course, it is not. Regardless of how you have the cells formatted for display, Excel uses the underlying value when doing calculations. In the example, you are not really adding 0.3 + 0.3 + 0.3, but rather

0.333333333333333 + 0.333333333333333 + 0.333333333333333,

whose sum is (almost) 1.0.

Excel does offer an option called "Precision As Displayed", which you can enable from Calculate tab on the Options dialog (Tools menu). Enabling this option forces Excel to use the displayed values in its calculations, rather than the underlying numbers. With this option enabled, the example above would indeed SUM to 0.9. You must be very careful when using this option, however. Once enabled, all precision is lost, and cannot be regained. All cells are calculated based on the displayed value. This option applies to the entire workbook, not to a specific cell or range of cells.

Floating Point Numbers

Excel, like nearly every other computer program, uses the IEEE Standard for Double Precision Floating Point numbers. This standard is described in detail, at the bit level, in a later section of this article. We can generalize it, though, to describe how Excel stores fractional numbers. Just as computers store integers as binary numbers, they store fractional numbers as binary fractions.

Computers store an integer (whole number) value as (x*1 + x*2 + x*4 + x*8 + x*16 etc) where x is the state of the bit. If the bit is on, x=1. If the bit of off, x=0. In this notation, any integer can be stored exactly. For example, the number 13 is stored in binary as 1101 which indicates, reading from left to right, 1*8 + 1*4 + 0*2 + 1*1 = 13.

Fractional numbers are stored in a similar manner. In the binary system, fractional numbers are stored as the sum of a series of fractions: (x*1/2 + x*1/4 + x*1/8 + x*1/16 etc) where x is the state of the bit. If the bit is on, x=1. If the bit of off, x=0. Unlike integers, however, not every fractional value can be stored exactly accurately. For example, it is impossible to store the number 1/10 = 0.1 in binary form. A close approximation is (0*1/2 + 0*1/4 + 0*1/8 + 1*1/16 + 1*1/32 etc). Computers carry this operation to the equivalent of 15 decimal places. Even with this accuracy, many numbers are represented as an approximation of their "true" or "analytic" value. For example, it is impossible to accurately describe the number 1/10 in 8-byte (or any length) binary notation. Floating point numbers can come extremely close to representing that number, but there will always be some very small error.

It is important to note that these errors and limitations on fractional numbers are not really errors at all. Nor are they "bugs" in the programs. These are well-known and well-documented limitations of the floating point arithmetic systems in almost every software package and hardware device.

Worksheet Functions For Rounding

Excel provides you with several functions to handle rounding. These functions are listed below.

NOTE: The MROUND function is part of the Analysis ToolPak Add-In for Excel. You must have this package installed in order to use these functions. To install the ATP, go to the Tools menu, select Add-Ins, and place a check next to the Analysis ToolPak item.

See the on-line help for more information about these functions.

 IEEE Floating Point Standard

The section describes the internal format of 64-bit double precision floating point variables. The layout of a double is as follows:

Bit 63

 62                52 51                             0
Sign Exponent Mantissa

A number n is expressed in floating point format at

n = (-1)^s * m * 2^e

where s is the value of the sign bit, m is the mantissa, and e is the exponent.

The mantissa m is "normalized," which means that it is always scaled such that it is greater than or equal to 1, and less than 2. Therefore, the ones bit (2^0) is always set, and is not present in the actual number. This is called an implied bit. Since the mantissa is 52 bits, plus the implied ones bit, the precision of the number is stored to 53 bits, or 2^53 = 900,719,925,474,100, approximately 15 digits of precision.

The exponent e is "biased". The number stored in the exponent bits is the actual exponent plus 1023, which ensures that it will always be positive. The "unbiased" value of the exponent, after subtracting the 1023 bias, can be between –1022 and +1023.

(The cases of all exponent bits equal to 0 or equal to 1 are reserved. When the exponent bits are all zero, the exponent is treated as being fixed at –1022 and the mantissa is assumed to be between 0 and 1. This is called an "unnormalized" number, and is how the value 0 is stored in a double. This allows extremely small numbers to be stored, but with less precision. When all exponent bits are 1, this indicates that an error has occurred and for representing positive or negative infinities.)

Therefore, the value 2^e can be between 2^(-1022) and 2^1023, or approximately 2.2*10^(-308) and 8.9*(10^307).

Since the mantissa has a maximum value of just less than 2, (actually 2 – 1/(2^52) ), the maximum value of the floating point number is about 1.8*(10^308).

Example: The number 10.4 can be expressed as a double precision floating point number as follows. As a binary fraction, 10.4 = 1010.011001100110011….

The number 10 is represented in binary as 1010, and the number 0.4 is represented as .011011011…. Of course, this is only a very close approximation of 0.4, since it cannot be stored exactly. There is no sum of (1/2 + 1/4 + 1/8 …) that is exactly equal to 0.4.

To normalize the number, the "binary point" is shifted three places to the left, and the result multiplied by 2^3: 1.010011001100110011… We can see then that

10.4 = (-1)^0 * (1.010011001100110011…) * 2^3

Therefore, the sign is 0, the exponent is 3, and the mantissa is 1.010011001100110011…. Since the mantissa is always greater than or equal to 1, only the portion to the right of the binary point is stored: 010011001100110011…. Since the exponent is 3, and 3 + 1023 = 1026, the exponent is stored in the variable as 10000000010.

The number 10.4 is stored as

0100000000100100110011001100110011001100110011001100110011001100

In Hex notation, this would be

4024CCCCCCCCCCCC

The first 0 is the sign bit. The bold portion is the exponent, and the rest is the mantissa.

Single precision floating point numbers, or "singles" are similar to doubles, except that they occupy 32 bits, rather than 64 bits, and have an 8 bit exponent rather than an 11 bit exponent. The bias of the exponent is 127 rather than 1023.