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.
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.
Search JabSto ::

Custom Search