# Formula Errors

In this chapter, we’re going to take a look at some of the common errors that occur when formulas are entered into Excel incorrectly.

Use this workbook for the chapter.

Note: This is the same workbook used in the If Statements chapter.

## Four Common Errors

A common problem that can produce errors is misspelling. For instance, let’s say we’d like to take the average of all of the ages. If you complete the formula but “average” is spelled incorrectly, Excel cannot recognize the formula itself. It will give you an error that says “#NAME?” This can be corrected by typing in the correct spelling of the formula and hitting Enter.

Another type of error is called the value error. Let’s say you are trying to calculate the sum of a few random cells, but rather than using the sum formula, you just select cells and put the plus sign between them. If you select a cell that is text rather than numbers, it will show you an error that says, “#VALUE!” This is because it cannot calculate text. However, if you use the =SUM formula and include a text cell, it will calculate the sum because it has the capability to add together only the numeral cells. In the case of a value error, simply get rid of the cell that contains text.

A third error type is the divide-by-zero error. For instance, if you type, “=100/0,” it will give you an error that says, “#DIV/0!” Since Excel can’t calculate an infinite number, it gives this error. For example, this could occur with sales history. If you’re trying to calculate the difference in sales between four years, in year zero, you have no “last-year’s sales.” For example, in the following data, if you divide the new year by the old year and Subtract one, it will give you the percentage increase. (See Figure 18.1)

However, if you drag that formula up to the other years, when it gets to 1, there is no former year to divide it by, and you get a divide-by-zero error.

The fourth type of error that we’ll look at is called the reference error. Let’s suppose we reference two cells and add them together, and then delete one of the cells being referenced or the row that a reference cell is in by right-clicking and selecting “delete the cell” or “delete the row.” It will give you an error that looks like “#REF!” in place of the referenced cell. This is because that cell technically no longer exists. However, if you select a referenced cell and just press the Delete key, the formula still calculates. This is because the Delete key only gets rid of the text, not the cell itself, whereas if you right-click and choose delete, it would delete the cell itself, and the formula would no longer understand what it is supposed to be referencing.

## IFERROR Formula

As we think about other ways to handle these various errors that we’ll see in Excel, there’s a formula to be made aware of called “IFERROR.”

1. To get to it, type =IFERROR(.
1. You’ll notice it asks for a value as the first argument, and the second argument is for if there’s an error. (See Figure 18.2)

2. For example, if the formula was to calculate the sum of two salaries, let’s say we want to anticipate there could be some sort of error in the future.
3. Reference cells G13 and G17 to add them together for the first argument.
4. For the second argument, put in a text string saying, Error Found. (See Figure 18.3)