Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
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.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 74986 Viewers
80 Video lessons · 129950 Viewers
52 Video lessons · 64131 Viewers
59 Video lessons · 49922 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.