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.
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.
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:
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: