Part IV: Using Advanced Excel Features
650
l (^) Semantic errors: An example is a function name that is spelled incorrectly. Excel will
attempt to interpret it as a name and will display the #NAME? error.
l (^) 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.
l 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 formula as an Array formula, and you may get an error or incorrect results.
l (^) Incomplete calculation errors: The formulas simply aren’t calculated fully. Microsoft has
acknowledged some problems with Excel’s calculation engine in some versions of Excel.
To ensure that your formulas are fully calculated, press Ctrl+Alt+Shift+F9.
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 mis-
matched parentheses. Other syntax errors also usually result in an error display in the cell.
The following sections describe common formula problems and offers 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 exception 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:A500A 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 lowercase. 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 are mismatched. The result dis-
plays 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.