Precision of Results

When you apply a formula, Excel looks at the actual value held in the cell, not the number displayed.  This means that if you have formatted a number to no decimal places and a whole number is displayed, the total will not necessarily be the same as the sum of the resulting whole numbers as all the fractional parts will have been added as well.  Look at the example below, if you added column C with a calculator you would obtain the answer £233.36 and not £233.34.  Excel gives a lower figure as the numbers displayed are the result of rounding up to display to 2 decimal places and the actual figures are slightly smaller.

In situations where you do want your total to be the same as the total someone sitting down with a calculator and adding up the values on the printout would achieve you have to set Excel to use the displayed values rather than the true values in formulas.  To do this, click on the Tools drop-down menu and choose Options, a window appears as shown below:

Click to place a tick in the Precision as displayed box at the bottom of the bottom of the window and click OK.  Take great care if you decide to do this, though, as you will lose the unseen parts of all the values on that worksheet   It is probably a good idea to use this facility on a copy of your real data so that you do not lose your original values.

When you set this option you will be warned that the accuracy will be lost in your calculations

If you are not sure that you have created a copy of your valuable data, click Cancel when you see this box to avoid losing your accurate data.

Here is the same data that was shown above with the precision as displayed setting applied.  Look at the difference in the totals!

Click here to return to the Excel index.