Microsoft Office Tutorials and References

In Depth Information

**Formula Problems and Solutions**

h
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.

h
The cell contains a formula that returns an invalid date or time. For example, Excel does

not support dates prior to 1900 or the use of negative time values. Attempting to display

either of these will result 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 is not 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)

Using Formula AutoCorrect

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

offers a suggested correction. The accompanying figure shows an example of a proposed correction.

Exercise caution 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 syntactically correct — but not what I intended. The correct formula is as follows:

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