Microsoft Office Tutorials and References
In Depth Information
Formula Problems and Solutions
Syntax errors are usually the easiest to identify and correct. In most cases, you will 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 remainder of this section describes some 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: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 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
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.
Excel can help you with mismatched parentheses. When you edit a formula, use the
arrow keys to move the cursor to a parenthesis and pause. Excel displays it (and its
matching parenthesis) in bold for about one second. In addition, nested parentheses
appear in a different color.
Cells are filled with hash marks
A cell displays a series of hash marks (#) for one of two reasons:
 
Search JabSto ::




Custom Search