How to correct Rounding Errors in Floating Point Arithmetic?

[Vandam]

Hello friends,
I am doing some arithmetic functions in an Excel and also facing some issues. Since I am not that much good enough, I am not able to fix the problems that I am facing. Actually I am not able to correct rounding errors in floating point. I have tried a lot of different things, but not getting the proper results. So please tell me how to correct rounding errors in floating point arithmetic..?? Thanks in Advance.!!

[Glenny]

I would like to explain exactly what is the main reason for the incorrect results. Many combinations of arithmetic operations on floating point numbers in Microsoft Excel and Microsoft Works can produce results that appear to be incorrect by very small amounts. For example, the equation :

• = 1 * (.5 -. 4-.1)

can evaluate the quantity (- 2.78E - 17), or-0.0000000000000000278 instead of 0.

[ThoMas321]

This behavior is not a problem or a limitation of Excel or Works; this behavior occurs because the 754 floating point standard Institute of Electrical and Electronics Engineers (IEEE) requires that the numbers are stored in binary format. Microsoft Excel was designed around the IEEE 754 specification with respect to storing and calculating floating-point numbers. The IEEE 754 is a method of storing floating point numbers in compact form, which is easy to manipulate. This standard uses Intel coprocessors and most PC based programs that implement floating point mathematics.

[chroma]

To minimize the effects of inaccuracy of floating point arithmetic storage, use the Round () function to round numbers to the number of decimal places is required for calculation. For example, when working with currency, you should probably round off to two decimal places :

= ROUND (1 * (0.5-0.4-0.1), 2)

[Raine]

You can often avoid errors affecting their work using the display precision floating point rounding option. This option causes the value of each number in the spreadsheet that the precision displayed in the spreadsheet. To use the precision option screen, follow these steps :

1. On the Tools menu, click Options.
2. In the calculation sheet in Workbook options, click to select the check box screen accuracy.
3. Click OK.

[Sam.D]

The steps for the precision option, that is provided by 'Raine' would be useful for Excel 2003. If you are having Excel 2007, then follow the steps mentioned below :

1. Click the Microsoft Office Button, Excel Options, and click Advanced options.
2. In calculating this workbook section, select the book you want, click the check box to establish precisely as shown and then click OK.

If you use the screen accuracy, you should format the numbers with specific number format. Using the precision option as shown can have a cumulative calculation effects that can make the data more and more inaccurate over time. Use this option only if you are sure that the sample percision maintain the accuracy of the data.