You can frequently prevent floating point rounding errors from affecting your work by using the Set precision as displayed option before you apply a number format to your data. This option forces the value of each number in the worksheet to be at the precision that is displayed on the worksheet.
Note: Using the Set precision as displayed option can have cumulative calculation effects that can make your data increasingly inaccurate over time. Use this option only if you are certain that the displayed precision will maintain the accuracy of your data.
-
In your worksheet, select File > Options.
-
Select Advanced, and then under When calculating this workbook, select the Set precision as displayed check box, and then select OK.
-
Select OK.
-
In your worksheet, select the cells that you want to format.
-
On the Home tab, in the Number section, select the Dialog Box Launcher.
-
In the Category box, select Number.
-
In the Decimal places box, enter the number of decimal places that you want to display.
Tip: To minimize any effects of floating point arithmetic storage inaccuracy, you can also use the ROUND function to round numbers to the number of decimal places that is required by your calculation.
