Correcting Formula and Function Errors

Click a cell with a #VALUE! message and review the cell's formula in the Formula bar. Can you find the error?

To add the values C2 through C12 and divide by 10, the formula must include “SUM” in the function. Type SUM in the Formula Bar and press Enter.

Click a cell with a #NAME? message. Move the mouse pointer over the cell to review a ScreenTip about the error.

INTRODUCTION

Excel notifies you when there are errors in your data by displaying different error descriptions. #VALUE? means the formula in the cell contains either nonnumeric data or cell/function names that cannot be used in the calculation. #NAME? means the formula contains incorrectly spelled cell/function names. #REF! indicates that the formula contains a reference to a cell that isn't valid. #### means the column is not wide enough to display the data. #DIV/0! means that the formula is trying to divide a number by 0 or that the formula is referencing an empty cell.

TIP

Tracing Errors

Check formulas by tracing precedents (all cells that are referenced [in order] in the formula). You can also trace dependents (start with a cell that is referenced in a formula, and then trace all the cells that reference that cell).

In this example, avg is not the correct name of the desired function; the correct name is average. In the Formula bar, replace avg with average and press Enter.