Excel 2010 Bible

Part IV: Using Advanced Excel Features

658

Notice that the negation operator symbol is exactly the same as the subtraction operator symbol.

This, as you may expect, can cause some confusion. Consider these two formulas:

=-3^2

=0-3^2

The first formula, as expected, returns 9. The second formula, however, returns –9. Squaring a

number always produces a positive result, so how is it that Excel can return the –9 result?

In the first formula, the minus sign is a negation operator and has the highest precedence. However,

in the second formula, the minus sign is a subtraction operator, which has a lower precedence than

the exponentiation operator. Therefore, the value 3 is squared, and then the result is subtracted

from 0 (zero), which produces a negative result.

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 32.4 shows a worksheet with the following formula entered into each cell in the

range B2:B4:

=1/3

FIGURE 32.4

A simple demonstration of numbers that appear to add up incorrectly.