Microsoft Office Tutorials and References
In Depth Information
Formula Problems and Solutions
Using parentheses, as shown in the following formula, causes Excel to interpret the operator as a
minus sign rather than a negation operator. This formula returns 9:
Formulas are not calculated
If you use custom worksheet functions written in VBA, you may find that formulas that use these
functions fail to get recalculated and may display incorrect results. To force a single formula to
be recalculated, select the cell, press F2, and then press Enter. To force a recalculation of all
formulas, press Ctrl+Alt+F9.
Actual versus displayed values
You may encounter a situation in which values in a range don’t appear to add up properly. For
example, Figure 21-4 shows a worksheet with the following formula entered into each cell in the
Figure 21-4: A simple demonstration of numbers that appear to add up incorrectly.
Cell B5 contains the following formula:
All the cells are formatted to display with three decimal places. As you can see, the formula in cell
B5 appears to display an incorrect result. (You may expect it to display 0.999.) The formula, of
course, does return the correct result. The formula uses the actual values in the range B2:B4, not
the displayed values.
You can instruct Excel to use the displayed values by selecting the Set Precision as Displayed
check box on the Advanced tab of the Excel Options dialog box. (Choose File
display this dialog box.) This setting applies to the active workbook.