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: