Microsoft Office Tutorials and References

In Depth Information

When in doubt (or when you simply need to clarify your intentions), use parentheses to ensure that operations

are performed in the correct order. For example, the following formula multiplies A1 by A2 and then adds 1 to

the result. The multiplication is performed first because it has a higher order of precedence.

=1+A1*A2

The following is a clearer version of this formula. The parentheses aren't necessary — but in this case, the order

of operations is perfectly obvious.

=1+(A1*A2)

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 the result is subtracted from zero, producing a negative result.

Excel is a bit unusual in interpreting the negation operator. Other spreadsheet products

(for example, Lotus 1-2-3 and Quattro Pro) return –9 for both formulas. In addition, Ex-

cel's VBA language also returns –9 for these expressions.

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. For example, assume that you wrote a VBA function that

returns the number format of a referenced cell. If you change the number format, the function will continue to

display the previous number format. That's because changing a number format doesn't trigger a recalculation.

To force a single formula to be recalculated, select the cell, press F2, and then press Enter. To force a recalcula-

tion of all formulas, press Ctrl+Alt+F9.

See Part VI for more information about creating custom worksheet functions with VBA.

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

22-4 shows a worksheet with the following formula entered into each cell in the range B2:B4: