Part II: Working with Formulas and Functions
Circular references A circular reference occurs when a formula refers to its own cell,
either directly or indirectly. Circular references are useful in a few cases, but most of the
time, a circular reference indicates a problem.
Array formula entry error When entering (or editing) an array formula, you must press
Ctrl+Shift+Enter to enter the formula. If you fail to do so, Excel doesn’t recognize the for-
mula as an array formula, and you may get an error or incorrect results.
Incomplete calculation errors The formulas simply aren’t calculated fully. To ensure that
your formulas are fully calculated, press Ctrl+Alt+Shift+F9.
Refer to Chapter 18, “Understanding and Using Array Formulas,” for an introduction to array formulas.
Syntax errors are usually the easiest to identify and correct. In most cases, you’ll know
when your formula contains a syntax error. For example, Excel won’t permit you to enter a
formula with mismatched parentheses. Other syntax errors also usually result in an error
display in the cell.
The following sections describe common formula problems and offer advice on identifying
and correcting them.
Mismatched parentheses
In a formula, every left parenthesis must have a corresponding right parenthesis. If your
formula has mismatched parentheses, Excel usually won’t permit you to enter it. An excep-
tion to this rule involves a simple formula that uses a function. For example, if you enter
the following formula (which is missing a closing parenthesis), Excel accepts the formula
and provides the missing parenthesis:
=SUM(A1:A500
A formula may have an equal number of left and right parentheses, but the parentheses
may not match properly. For example, consider the following formula, which converts a text
string such that the first character is uppercase and the remaining characters are lower-
case. This formula has five pairs of parentheses, and they match properly:
=UPPER(LEFT(A1))&RIGHT(LOWER(A1),LEN(A1)-1)
The following formula also has five pairs of parentheses, but they’re mismatched. The result
displays a syntactically correct formula that simply returns the wrong result:
=UPPER(LEFT(A1)&RIGHT(LOWER(A1),LEN(A1)-1))
Often, parentheses that are in the wrong location will result in a syntax error, which is
usually a message that tells you that you entered too many or too few arguments for a
function.
