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.
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
Excel then proposed the following correction to the formula:
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: