Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

Errors happen. And when you create Excel formulas, errors happen very frequently. This chapter describes common formula errors and also discusses tools and methods that you can use to help create formulas that work as they are intended to work.

Formula Debugging?

The term debugging refers to the process of identifying and correcting errors in a computer program. Strictly speaking, an Excel formula is not a computer program. Formulas, however, are subject to the same types of problems that occur in a computer program. If you create a formula that does not work as it should, you need to identify and correct the problem.

The ultimate goal in developing a spreadsheet solution is to generate accurate results. For simple worksheets, this is not difficult, and you can usually tell whether the results are correct. But as your worksheets grow in size or complexity, ensuring accuracy becomes more difficult.

Using a spreadsheet can be hazardous to your company's bottom line. It's tempting to simply assume that your spreadsheet produces accurate results. If you use the results of a spreadsheet to make a major decision, it's especially important to make sure that the formulas return accurate and meaningful results.

Researchers have conducted quite a few studies that deal with spreadsheet errors. Generally, these studies have found that between 20 and 40 percent of all spreadsheets contain some type of error. If this type of research interests you, I urge you to check out the Spreadsheet Research (SSR) Web site maintained by Raymond Panko of the University of Hawaii. The URL is

http://panko.cba.hawaii.edu/ssr/

Making a change in a worksheet-even a relatively minor change-may produce a ripple effect that introduces errors in other cells. For example, accidentally entering a value into a cell that previously held a formula is all too easy to do. This simple error can have a major impact on other formulas, and you may not discover the problem until long after you made the change-or you may never discover the problem.