Excel 2013: How to Use Goal Seek in Excel 2013
Using Goal Seek
Excel has a number of analytical tools and they fall under the heading WhatIf 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 WhatIf 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, WhatIf 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 WhatIf 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. WhatIf 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, WhatIf 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 DoubleClick 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.
Using Goal Seek provides you with indepth training on Business. Taught by Dennis Taylor as part of the Excel 2013 Essential Training
 Subjects:
 Business Education + Elearning
 Software:
 Excel
 Author:
 Dennis Taylor

1m 6s

 Welcome

43s


29m 37s

 What is Excel used for?

1m 49s

 Using the menu system

4m 30s

 The Quick Access Toolbar

4m 41s

 Using the Formula bar

1m 43s

 Using the Status bar

2m 24s

 Using the builtin help

2m 54s

 Creating new files

2m 11s


24m 1s

 Working with dates and times

3m 32s

 Using Undo and Redo

4m 50s

 Adding comments

2m 55s

 Using Save or Save As

3m 57s

30m 7s

 Using SUM and AVERAGE

3m 25s

46m 7s

 Designing borders

3m 26s

 Formatting numbers and dates

4m 31s

 Conditional formatting

4m 21s

 Creating and using tables

9m 59s

20m 40s

17m 51s

30m 30s

 Creating charts

4m 36s

 Exploring chart types

7m 47s

 Formatting charts

5m 42s


12m 49s

23m 0s

20m 25s

23m 50s

 Protecting workbooks

2m 40s

 Sharing workbooks

4m 7s

 Tracking changes

4m 32s

28m 32s

 Sorting data

6m 9s

 Using filters

6m 16s

 Removing duplicate records

2m 38s


35m 2s

 Creating PivotTables

8m 36s

 Manipulating PivotTable data

9m 47s

 Grouping by other factors

2m 33s

 Using PivotCharts

3m 59s


23m 29s

 Using Goal Seek

6m 8s

 Using Solver

6m 34s

 Using Scenario Manager

6m 11s

 Using Data Tables

4m 36s


24m 31s

 Definition and examples

6m 48s

 Creating a simple macro

7m 0s

 Running a macro

10m 43s


29s

 Next steps

29s

Related Courses

Excel 2010 Essential Training6,010,917 Views

Word 2013 Essential Training1,684,493 Views

Access 2013 Essential Training2,711,788 Views

PowerPoint 2013 Essential Training1,670,720 Views

Excel 2013: Pivot Tables in Depth1,335,480 Views

Access 2013 Essential Training2,711,788 Views

Word 2013 Essential Training1,684,493 Views

Excel 2010 Essential Training6,010,917 Views

PowerPoint 2013 Essential Training1,670,720 Views

Foundations of Programming: Fundamentals7,772,051 Views