Auditing Your Formulas

Your spreadsheet results are only as good as the data you give it and the formulas you create. Feed a spreadsheet the wrong data, and it will obviously calculate the wrong result. More troublesome is when you feed a spreadsheet the right data but your formula is incorrect, which produces a misleading and incorrect result.

Even if Excel appears to be calculating your formulas correctly, recheck your calculations just to make sure. Some common errors that can mess up your formulas include.

• Missing data: The formula isn't using all the data necessary to calculate the proper result.
• Incorrect data: The formula is getting data from the wrong cell.
• Incorrect calculation: Your formula is incorrectly calculating a result.

If a formula is calculating data incorrectly, you probably didn't type the formula correctly. For example, you may want a formula to add two numbers, but you accidentally typed in the formula to multiply two numbers instead. To check whether a formula is calculating data incorrectly, give it data that you already know what the result should be. For example, if you typed the numbers 4 and 7 into a formula that should add two numbers, but it returns 28 instead, you know it's not calculating correctly.

If your formula is correct but it's still not calculating the right result, chances are good it's not getting the data it needs from the correct cells. To help you trace whether a formula is receiving all the data it needs, Excel offers auditing features that visually show you which cells supply data to which formulas. By using Excel's auditing features, you can

• Make sure your formulas are using data from the correct cells.
• Find out instantly whether a formula could go haywire if you change a cell reference.