Part II: Working with Formulas and Functions
#VALUE! errors
A #VALUE! error is common and can occur under the following conditions:■ (^) An argument for a function is of an incorrect data type, or the formula attempts to
perform an operation using incorrect data. For example, a formula that adds a value
to a text string returns the #VALUE! error.
■ (^) A function’s argument is a range when it should be a single value.
■ (^) A custom worksheet function is not calculated. You can press Ctrl+Alt+F9 to force a
recalculation.
■ (^) A custom worksheet function attempts to perform an operation that is not valid.
For example, custom functions can’t modify the Excel environment or make
changes to other cells.
■ (^) You forget to press Ctrl+Shift+Enter when entering an Array formula.
Pay Attention to the Colors
When you edit a cell that contains a formula, Excel color-codes the cell and range references in the
formula. Excel also outlines the cells and ranges used in the formula by using corresponding colors.
Therefore, you can see at a glance the cells that are used in the formula.
You also can manipulate the colored outline to change the cell or range reference. To change the
references used in a formula, drag the outline’s border or fill handle (at the lower right of the outline).
This technique is often easier than editing the formula.
Operator precedence problems
As noted in Chapter 9, Excel has some straightforward rules about the order in which math-
ematical operations are performed. When in doubt (or when you simply need to clarify your
intentions), you should use parentheses to ensure that operations are performed in the cor-
rect 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+A1A2
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+(A1A2)
Notice that the negation operator symbol is 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