Chapter 19: Making Your Formulas Error-Free. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
19
Excel can help you with mismatched parentheses. When you’re editing a formula and you move the cursor over a
parenthesis, Excel displays it (and its matching parenthesis) in bold for about one-half second. In addition, Excel
color-codes pairs of nested parentheses while you’re editing a formula.
Using Formula AutoCorrect
When you enter a formula that has a syntax error, Excel attempts to determine the problem and offers
a suggested correction.
Be careful when accepting corrections for your formulas from Excel because it doesn’t always guess cor-
rectly. For example, imagine that you entered the following formula (which has mismatched parentheses):
=AVERAGE(SUM(A1:A12,SUM(B1:B12))Excel then proposes 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 syntactically correct, but it’s not what you intended. The correct formula is as follows:
=AVERAGE(SUM(A1:A12),SUM(B1:B12))Cells are filled with hash marks
A cell is filled with a series of hash marks (#) for one of two reasons:■ (^) The column is not wide enough to accommodate the formatted numeric value.
To correct it, you can make the column wider or use a different number format.
(See Chapter 23, “Visualizing with Custom Number Formats and Shapes.”)
■ (^) The cell contains a formula that returns an invalid date or time. For example,
Excel doesn’t support dates prior to 1900 or the use of negative time values. A for-
mula that returns either of these values results in a cell filled with hash marks.
Widening the column won’t fix it.
Blank cells are not blank
Some Excel users have discovered that by pressing the spacebar, the contents of a cell seem
to erase. Actually, pressing the spacebar inserts an invisible space character, which isn’t
the same as erasing the cell.
For example, the following formula returns the number of nonempty cells in range A1:A10.
If you “erase” any of these cells by using the spacebar, these cells are included in the
count, and the formula returns an incorrect result:
=COUNTA(A1:A10)