Rounding In Excel

Under some circumstances, Excel may seem to return a wrong value for a calculation. In nearly all cases, this perceived error is really the result of one of two causes: the difference between the value displayed in a cell and the actual value of a cell, or a limitation in the accuracy of the way fractional number are stored in a computer.

Excel always stores the value in a cell to the fullest precision possible. That precision, the number of digits to both the left and the right of the decimal point that can be accurately used, is determined by the IEEE Double Precision Floating Point data type, described below. This standard allows for 15 digits of precsion. Excel will calculate with full precision even if you are formatting to display fewer decimal places. For example, enter the formula =1/8 in cells A1:A8. Then, in A9, enter =SUM(A1:A8). If these cells are formatted for at least 3 decimal places, Excel will display 0.125 in cells A1:A8 and 1.000 in A9. This is correct. Now, change the number format of cells A1:A9 to display only 2 decimal places. This will cause A1:A8 to display 0.13 and E9 to display 1.00. This may seem incorrect at first glance. The equation =0.13*8 is 1.04 not 1.00. This apparent error is due to the fact that Excel uses full precision values in calculations, not the displayed value. In the formula =SUM(E1:E8), Excel uses the values 0.125 in the calculation, not the displayed values 0.13.

It is possible to configure Excel to use the displayed values in calculation. This is control by the Precision As Displayed option on the Calculation tab of the Excel Options dialog on the Tools menu. However, this result in a loss of accuracy and can lead to other errors.

Excel, like nearly all computer software, uses what are called IEEE Double Precision Floating Point numbers. These data types are an industry standard from the Institute of Electrical and Electronics Engineers (IEEE). This is a nearly universally accepted numeric data type -- it is not specific to Excel or Microsoft. The a variable declared as Double uses the IEEE Double Precision Floating Point specification.

To understand Floating Point numbers, we need to take a quick review of binary numbers. A positive integer (whole number) is stored as a binary sequence (0s and 1s) that fill the polynomial (for a Long type variable)

Here, each N is either 1 or 0. Every positive whole number between 0 and 2,147,483,647 can be represented this way. (Negative number are stored in a slightly different manner -- we won't discuss that here.)

Fractional number are stored, in an oversimplication, as

Here, not every fractional number can be accurately represented. In the decimal system, the number 1/3 cannot be represented exactly. No matter how far out you carry the representation 0.3333333..., it is still an approximation of exactly 1/3. In a similar manner, most numbers cannot be stored exactly in the binary fractional representation. Indeed, nearly all fractional numbers are an approximation of the actual number.