Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Excel has a number of analytical tools and they fall under the heading What-If Analysis. You'll see these on the Data tab in the ribbon. One of them is GoalSeek. And the description we see on the screen doesn't really do it justice. Find the right input for the value you want. In this worksheet called GoalSeek, there's a function in cell M5. You may have seen it; it's called PMT. What we're trying to do in this particular cell is calculate the monthly payment for a loan of $24,000 based on a 60 month term.
Interest rate is 3.5%. The answer that we get here is 437. So, if you borrow $24,000, pay it off over five years at 3.5% interest then, monthly payment is $437. Imagine a situation where you want a different answer. Maybe it's based on a real life wish that you want this to be lower or maybe you're saying, I know I was planning all along on spending 500 month, let's just make this answer to be 500 or 400 as the case may be. GoalSeek feature in a certain sense let's us work backwards.
It doesn't use sophisticated math, it uses brute force. We're going to say, we want this to be a different answer. Click this cell on the Data tab, choose What-If Analysis, choose GoalSeek. Here's the Goal Seek dialogue box. We want to set this cell M5 to be the value 500. By changing which cell, Goal Seek only allows us to change one cell. The function in cell M5 is based on data from M2, M3 and M4.
We can only change one of these. Suppose it's the principal at cell M4. If we're going to be spending 500 a month, we will be able to borrow more but how much? Click OK, and we see that amount 27,485. If we click OK, we keep the result. If we click Cancel we return to the original values. You might want to try this feature changing just the principal as I did or possibly change in the rate. You might not have control over that. If the borrowing institution allows you to change the term but keep the same amount, you might want to cancel, try this again, What-If Analysis, GoalSeek.
This time let's set the cell to be equal to the value 500, based on the term changing. Click OK. So, we could pay $500 instead of 437 and then pay off our loan in 52 months instead of 60. So, there are certainly different ways to use this. Let's take another case, scrolling down in the same worksheet. We're taking a course somewhere and here are the scores we've got on various tests. At the bottom here, in cell M14 is a formula that's averaging the cells, and recognize that it's averaging the empty cell as well; and the way this function works, if you work with it, you know that Excel adds up the data that it sees but it divides by the number of cells that have data.
So currently it's adding up the numbers and dividing by 6, but as soon as we put a number in this cell M13, the average will add up those numbers and divide by 7. So we want this average to be 90, so it can get an A. We'll go to What-If Analysis, GoalSeek, set this cell M14 to be equal to 90 by changing which cell, the cell that's currently empty, M13. Click OK, and oddly enough it seems to take a while to get there.
And finally when it does get there we get our answer and it's not too satisfactory, we got to get 102, well we can't do that. So, let's cancel this. Maybe if we get 89.51 we'll get our A, so let's try this again. What-If Analysis, GoalSeek, set this cell M14 to be equal to 89.5, by changing which cell, this empty cell again M13, click OK. So, if we ace this final test and get a 99, looks like we got to get that then we'll get our 89.5 average.
Maybe we'll get the A after all. Now, let's take more business like situation here. Column H has a bunch of salaries, over 700 of them. New salaries that have been calculated based on the old salary and a percent increase of 2.7%. So, every one of these salary calculations is based at least partially on this 2.7% in cell J2. A total in J1 of all those entries in column H is 38,889,000. Let's imagine you are the chief financial officer of this organization and you say, well, in our prior planning we were planning on 39 million expenditure on salaries.
Let's make this be 39 million. In other words, let's recalculate all 700 or so of these formulas so that our total is now 39 million. So when you use GoalSeek here, What-If Analysis, GoalSeek, and we want that cell J1 to be equal to 39 million 39,000,000 you don't to put in the comas but you can. By changing which cell, the cell that's got the percent in it. Now you saw how long it took with our example of those test scores, how long is this going to take? Click OK, it's done.
There it is, if we want to keep it we'll click OK. Looks like 3% is what we need here. Now, when you use GoalSeek, don't assume these answers are exactly what you see. If I click the cell, this is the one that change from 2.7%, I click this cell, look in the formula bar. It's really 2.99175 et cetera; it's a big long number up there. Now, someone has to make an exacted decision and decide what to do, so you might just Double-Click and change that to 2. 99 and it's probably going to be close enough.
There we go, but as I press Enter our total will change. It'll be a bit lower, not a whole lot lower. So, you can see how this feature works. It's ideal for working backwards so to speak. We want a different answer for our formula and we can do this by using GoalSeek to change one of the cells, that's a component of that formula.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 69972 Viewers
80 Video lessons · 127391 Viewers
52 Video lessons · 62346 Viewers
59 Video lessons · 48043 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.