Microsoft Office Tutorials and References

In Depth Information

**Understanding the precision of numeric values**

answer is close enough. Excel can perform iterations in seconds, but in complex circular

situations, you might want to set the Calculation option to Manual; otherwise, Excel

recalculates the circular references every time you make a cell entry.

The Solver add-in, a “what-if” analysis tool, offers more control and precision when working

with complex iterative calculations. For details, see “Using the Solver” in Chapter 18.

Understanding the precision of numeric values

Here are three interesting facts about numeric precision in Excel:

Excel stores numbers with as much as 15-digit accuracy and converts any digits after

the fifteenth to zeros.

●

●
Excel drops any digits after the fifteenth in a decimal fraction.

●
Excel uses scientific notation to display numbers that are too long for their cells.

TROUBLESHOOTING

Rounded values in my worksheet don’t add up

Your worksheet can appear erroneous if you use rounded values. For example, if you

use cell formatting to display numbers in currency format with two decimal places,

Excel displays the value
10.006
as the rounded value $10.01. If you add 10.006 and

10.006, the correct result is 20.012. If all these numbers are formatted as currency,

however, the worksheet displays the rounded values
$10.01
and
$10.01
, and the rounded

value of the result is
$20.01
. The result is correct, as far as rounding goes, but its

appearance might be unacceptable for a particular purpose, such as a presentation or

an audit.

You can correct this problem by changing the currency format, or you can click the

File tab, Options, Advanced category. In the When Calculating This Workbook area,

select the Set Precision As Displayed check box. However, you should select this check

box only with extreme caution because it permanently changes the underlying values

in your worksheet to their displayed values. For example, if a cell containing the value

10.006
is formatted as currency, selecting the Set Precision As Displayed check box

permanently changes the value to
10.01
. For more information, see “Formatting numbers”

in Chapter 9.