Tip:
Highlight text to annotate it
X
Sometimes you'll look over a formula, verify that it uses the proper inputs, but
still find that it returns an incorrect result. If that's the case, you can move to the formula,
with one calculation at a time to identify the problem.
Some of the tactics you can use are setting up the formula with inputs that you know
will generate a specific result. If you don't get the result you expect, then
you know there is a problem with the formula. Then you can step through the formula with
one calculation at a time, I'll show you how in a moment, attempt to identify at
which point the formula generates an incorrect result while you are stepping through
it and once you've identified the problem, edit the formula and step through
it again to ensure that you're getting the right result for the right reasons.
As an example of a formula that you might evaluate, take a look at the formula
in cell B7, which is Monthly Payment. Now we have a $245,000 loan with a 6.9% interest
rate being paid off over three years. There is no way that you would have a Monthly
Payment of $0. It just doesn't happen.
So, how do you find the error in the formula? To do that, you can go to the Evaluate Formula
dialog box. So, I click the cell that contains the formula
I want to evaluate and then on the Formulas tab, in the Formula Auditing
group, click Evaluate Formula. The Evaluate Formula dialog box displays the
formula and allows you to go through and determine the value step by step.
So, we'll start out by clicking Evaluate and it pulled the value from B4,
which is 6.9% or 0.069. Next step is to divide that by 12, with the
result you see here and when I click Evaluate again, it will display the number
of years, which is in cell B5. Click Evaluate again.
You get 36, which is the number of payments. You made 12 payments per year, over three
years, that's 36. Then you have the Loan Principal and here's
where we see the error. Instead of a Loan Principal here in cell B3,
I have $245,000, it's 0. So, I have already found the error but just
to show you how it works, when you evaluate the rest of the formula, I'll click
Evaluate again and you get a value of $0. So, I'll click Close and edit this formula
so that the Principal is pulled from cell B3 as opposed to cell B2. Press Return
and we get the right result. I won't go through all the steps of the formula
again, but you could use the Evaluate Formula dialog box to determine that
you were getting the proper result for the right reasons.
Evaluating Excel formulas is a powerful technique you can use to verify that a formula produces
the correct result. You can also use formula evaluation to understand
how a formula's logic works. If you encounter a formula that uses a function
you haven't used before, take the time to look at the function in the Excel
Help files and step through the formula so you'll have a better idea of why
the formula is in the workbook and how it derives its result.