Page 93 - Excel 2007 Bible

07_044039 ch03.qxp 11/21/06 10:55 AM Page 50
Part I
Getting Started with Excel
When Numbers Appear to Add Up Incorrectly
pplying a number format to a cell doesn’t change the value — only how the value appears in the work-
Asheet. For example, if a cell contains 0.874543, you may format it to appear as 87%. If that cell is used in
a formula, the formula uses the full value (0.874543), not the displayed value (87%).
In some situations, formatting may cause Excel to display calculation results that appear incorrect, such as
when totaling numbers with decimal places. For example, if values are formatted to display two decimal
places, you may not see the actual numbers used in the calculations. But because Excel uses the full precision
of the values in its formula, the sum of the two values may appear to be incorrect.
Several solutions to this problem are available. You can format the cells to display more decimal places. You
can use the ROUND function on individual numbers and specify the number of decimal places Excel should
round to. Or you can instruct Excel to change the worksheet values to match their displayed format. To do so,
access the Excel Options dialog box and click the Advanced tab. Check the Set Precision As Displayed check
box (which is located in the section named When Calculating This Workbook).
The top of the tab displays a sample of how the active cell will appear with the selected number format (vis-
ible only if a cell with a value is selected). After you make your choices, click OK to apply the number for-
mat to all the selected cells.
CAUTION Selecting the Precision As Displayed option changes the numbers in your worksheets to perma-
CAUTION
nently match their appearance onscreen. This setting applies to all sheets in the active work-
book. Most of the time, this option is not what you want. Make sure that you understand the consequences
of using the Set Precision As Displayed option.
CROSS-REF
CROSS-REF Chapter 11 discusses ROUND and other built-in functions.
The following are the number-format categories, along with some general comments:
n General: The default format; it displays numbers as integers, as decimals, or in scientific notation
if the value is too wide to fit in the cell.
n Number: Enables you to specify the number of decimal places, whether to use a comma to sepa-
rate thousands, and how to display negative numbers (with a minus sign, in red, in parentheses,
or in red and in parentheses).
n Currency: Enables you to specify the number of decimal places, whether to use a currency sym-
bol, and how to display negative numbers (with a minus sign, in red, in parentheses, or in red
and in parentheses). This format always uses a comma to separate thousands.
n Accounting: Differs from the Currency format in that the currency symbols always line up
vertically.
n Date: Enables you to choose from several different date formats.
n Time: Enables you to choose from several different time formats.
n Percentage: Enables you to choose the number of decimal places and always displays a percent
sign.

50