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:

=(-3)^2

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

range B2:B4:

=1/3

Figure 21-4:
A simple demonstration of numbers that appear to add up incorrectly.

Cell B5 contains the following formula:

=SUM(B2:B4)

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

➜

Options to

display this dialog box.) This setting applies to the active workbook.