Page 357 - Excel 2013 All-in-One For Dummies

Chapter 2: Logical Functions and Error Trapping
In This Chapter
✓ Understanding formula error values ✓ Understanding the logical functions ✓ Creating IF formulas that trap errors ✓ Auditing formulas
✓ Changing the Error Checking options ✓ Masking error values in your printouts
Troubleshooting the formula errors in a worksheet is the main topic of this chapter. Here, you see how to locate the source of all those vexing formula errors so that you can shoot them down and set things right! The biggest problem with errors in your formulas — besides how ugly such values as #REF! and #DIV/0! are — is that they spread like wildfire through the workbook to other cells containing formulas that refer to their error- laden cells. If you’re dealing with a large worksheet in a really big workbook, you may not be able to tell which cell actually contains the formula that’s causing all the hubbub. And if you can’t apprehend the cell that is the cause of all this unpleasantness, you really have no way of restoring law and order to your workbook.
Keeping in mind that the best defense is a good offense, you also find out in this chapter how to trap potential errors at their source and thereby keep them there. This technique, known affectionately as error trapping (just think of yourself as being on a spreadsheet safari), is easily accomplished by skillfully combining the IF function to combine with the workings of the original formula.
Understanding Error Values
If Excel can’t properly calculate a formula that you enter in a cell, the program displays an error value in the cell as soon as you complete the formula entry. Excel uses several error values, all of which begin with the number sign (#). Table 2-1 shows you the error values in Excel along with the meanings and the most probable causes for their display. To remove an error value from
a cell, you must discover what caused the value to appear and then edit the formula so that Excel can complete the desired calculation.