Microsoft Office Tutorials and References

In Depth Information

And even though a formula may have an equal number of left and right parentheses, the parentheses might 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)

Using Formula AutoCorrect

When you enter a formula that has a syntax error, Excel attempts to determine the problem and offers a sugges-

ted correction. The accompanying figure shows an example of a proposed correction.

Be careful when accepting corrections for your formulas from Excel because it does not always guess correctly.

For example, I entered the following formula (which has mismatched parentheses):

=AVERAGE(SUM(A1:A12,SUM(B1:B12))

Excel then proposed the following correction to the formula:

=AVERAGE(SUM(A1:A12,SUM(B1:B12)))

You may be tempted to accept the suggestion without even thinking. In this case, the proposed formula is syn-

tactically correct — but not what I intended. The correct formula is as follows:

=AVERAGE(SUM(A1:A12),SUM(B1:B12))

The following formula also has five pairs of parentheses, but they are mismatched. The result displays a syn-

tactically 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 ar-

row keys to move the cursor to a parenthesis and pause. Excel displays it (and its