With the Goal seek approach; you can simply follow the best practice to learn how you should allocate resources. It helps you in calculating backward from an end goal. Do you know how to use Excel goal seek?
With the Goal-seek Excel function, you can easily shape a plan to evaluate data with Excel. Actually, Goal-seek is a What-If analysis tool used when you know the outcome but you are not sure about the values needed to reach that outcome.
Let’s see how it works:
What is Goal Seek Function in Excel?
In Excel, you will find three built-in What-If Analysis tools:
- Data Tables
- Scenarios
- Goal Seek
All three of them are used in situations when you need to know how a change in specific values will affect the results. Remember that the Goal Seek function is useful only when you have an input value to define. In case, you can more than one value to define, you have to use the Solver add-in.
Suppose, you need a loan and you already know the output value. The final amount is like you can easily pay each month and you also know the input values of
- How much amount you need to borrow
- How much time (months) do you need to pay it back (with the Goal-Seek function)
- And the interest rate to keep the monthly payment within budget.
All these things are known to be key points when you need to use the Goal-seek function. Let’s see how you can use this function:
How to Use Excel Goal-Seek?
Let’s understand this function with simple and easy-to-learn examples:
Example 1
Below you can see a table in which votes are cast by the two candidates, A and B. Candidate B owns 10,000 votes and to win the election, 50.01% of votes are needed. Suppose you need to change the voting percentage of candidate A to make him win. For this, you will have to calculate the net figure of votes needed by candidate A to win.
First, we will calculate the current percentage of votes for both candidates. And for this, you may have to divide the votes one by one by the total votes. You will find that Candidate A owns 46.43% votes and he needs more than 3.67% votes to win the election.
Below are simple steps for you to follow:
- Open the Data tab and click on the Goal Seek option given in the What-if-analysis drop-down menu.
- You can see the goal-seek window in the following image:
- Now, enter $C$2 in the set cell box because C2 is the targeted cell.
- Here, add 50.01% in the “to value” box field. It is the “Set cell” value that we need.
- Now, add $B$2 in the “by changing cell” box. It is the cell that needed to be changed to impact the “set cell” C2. Press OK and you will get the results.
- According to the values changed, now, Candidate A would have 70,014 votes to win the election. And the percentage would be 50.01%.
- Press OK to save changes.
So, you see the net figure of votes and the percentage both are changed for candidate A according to the plan. Not only Candidate A but the voting figure of Candidate B is also changed. Ultimately, candidate A won the election.
Parameters to Follow for Excel Goal Seek
Below you can experience some of the most refined parameters in the Goal Seek dialog box:
Set Cell
This box needs you to add the cell reference of the formula you want to solve. It is considered the formula cell.
To value
This box needs you to have the desired output you want to get. It includes the value of the set cell you need to have.
By changing cell
This box needs you to have the reference of the input cell you need to adjust. In this cell, you need to change to impact the set cell.
Remember that the Goal-Seek function works only with one variable input. If you have more than one value, you will use another function, Solver.
Summary:
In this post, you have learned how to use Excel Goal Seek function. A clear overview of the function is given in this post which simply explains what it is and how it works. Hopefully, you will find this post helpful!
