Join Dennis Taylor for an in-depth discussion in this video Using Goal Seek, part of Excel 2016 Essential Training.
- If you're using an Excel formula and it gives you an answer that you'd rather not have you can turn to a feature called Goal Seek. In fact, we're on a sheet called Goal Seek in the workbook 13 - DataAnalysis. There's a formula in cell J4, it's using the function called PMT. It calculates the monthly payback on a loan. In the scenario here, we're looking at a rate of 3.5%, payoff over 60 months, we're borrowing $24,000. What will we pay each month? The payment function takes the rate, divide it by 12, the term in months, and then the principle.
If you want to see the answer as a positive number, put a minus in front of it, otherwise it will appear as a negative. $437. If we borrow $24000, pay it off over five years, that's 60 months, interest rate 3.5%. Let's say we've been thinking about this for a while and all along we were planning on spending $500 a month. We want this number to be 500. How can we change it? Well, we can change the rate, the term, the principle. But, what do we really have control over? Probably not so much the rate.
We could shop around at different Savings and Loans, Credit Unions, perhaps come up with a different rate. We do have some control over the term and we can certainly borrow more to make this be 500. But, rather than guessing and going back and forth here, let's just cut to the chase and say, let's make this be 500. We do it with a feature called Goal Seek. It's on the Data tab in the ribbon, under the category What-If Analysis. Goal Seek. Find the right input for the value you want.
We'd like to change this cell, J4, to be the value 500. Now, of course, you could imagine another situation that says I want this to be only 400. Well, we have to decide which of the three portions of the formula we want to change. And, we can only point to one of them. Let's say it's the principle. That means it's going to allow us to borrow more money. Click OK. The cell comes up with an answer immediately. We can now borrow 27,485 and if we keep the rate and the term the same that's what it's going to be.
If we want to keep this we'll click OK. If not, we'll Cancel it. We could try it a different way by varying the term. Maybe we don't need to borrow any more, but we do want to shorten the term. What-If Analysis, Goal Seek. Set this value to be 500. By changing which cell? This time the term. Click OK. We have an answer. That probably isn't exact but for now it's just fine. If you look on the formula bar it says 51.786.
Close enough. So, different approaches here. Here's another way to use it. You're taking a course somewhere, they're giving number grades. 100 is best, 90 and above is an A. You've got 88 here. This is averaging the scores including the empty cell, For the test you haven't taken yet. The average function, by the way, does consider all entries here and divides by the number of cells where there are values. So right now it is accurate. It's adding up these cells and dividing by six.
You haven't taken your final test yet. What do you need to get on that test for this to be a 90 or above? Well, you can start guessing here, that wouldn't take too long. But, let's try Goal Seek. Active cell is there. What-If Analysis, Goal Seek. Set this cell to the value 90. By changing which cell? The test we haven't yet taken, in cell J13 right there. Click OK. For some reason that seems to take a bit of time, doesn't it? There we are.
In order to get a 90 average we need to get 102 on the final test. Well, that's not going to work so well. What if we go back again? Cancel this. How about, make this be 89.5, maybe that will work. Back to What-If Analysis, Goal Seek. Set this cell to the value 89.5 by changing which cell? Same one, J13. Click OK. Once again, seems to take some time. Well, maybe we can pull this off. If we get 99 on our test looks like we'll get an 89.5, slightly higher average, that'll work, we'll get our A.
Good enough, let's keep it. Now, more business-like opportunity to use this. We've calculated some new salaries over here in column C. Everybody gets a 2.71% increase. What's the total of the new salaries? We can click column C and look in the status bar on the bottom of the screen, and there's that sum 61,000,000. But, let's put it on the worksheet right here as an actual function. We can type =sum or possibly just use the AutoSum button. It's found on the Formulas tab as well as the Home tab.
I'm going to click AutoSum here. And then, simply click column C. There's nothing in column C except those numbers and the text on top. So, there's the total. You're the Chief Financial Officer of this organization and based on sales and projections you've determined, we can afford to spend more than these salaries. Why not just make this 62,000,000? Well, we could start guessing by changing this percent. Every formula here in column C is based on the salaries in column B as well as that entry in E1.
In other words, the percent of increase. This one does, this one does, they all do. So, let's use Goal Seek to make this number be 62,000,000 by changing this value here. We don't want to be guessing. Let's just, as we said before, cut to the chase. Go to the Data tab, What-If Analysis, Goal Seek. We want this cell here to be equal to the value 62,000,000. 6-2-0-0-0-0-0-0. By changing which cell? Click here, click in cell E1.
OK, we're done. Notice how much faster that was. It had to deal with about 700 rows of data compared with what we were doing over here in column J. Not sure quite why that is. But, we've got an answer here and we can keep it by clicking OK. All the salaries in column C have been readjusted. Our formula, remember in cell E3, is tabulating the total of column C. Click OK. We're done. Now, it's not quite perfect. As I click cell E1, look in the formula bar, or as I double click right here we can see what's going on.
Not quite exact. Now, in a real life situation maybe you'll just say, well, why don't we make this 4.18, 4.19 maybe. Let's just try 4.18, that's going to be close enough. We want to have a number available that's reasonable to work with. If we keep this at 4.18, not quite perfect, but let's say close enough. Not quite 62,000,000, but it's only off by what, 2861, something like that. Anyway, we've got our answer here. We adjusted all of our entries in column C to get to a total that's just about what we want it to be.
Using the feature called Goal Seek, found in What-If Analysis on the Data tab.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros