How to Use Goal Seek in Microsoft Excel for What-If Scenarios

blognet Saturday, April 03, 2021 How to, Make it Useful Comment

Excel's What-If Analysis allows you to see how changing a cell can affect a formula's output. You can use Excel's tools for this purpose to calculate the effect of changing the value of a cell in a formula.

Excel has three types of What-If Analysis tools: Scenario Manager, Goal Seek, and Data Table. With Goal Seek, you can determine what input you need to convert the formula backward to a certain output.

The Goal Seek feature in Excel is trial and error, so if it does not produce what you want, then Excel works on improving that value until it does.

What Are Excel Goal Seek Formulas?

Goal Seek in Excel essentially breaks down into three major components:

Set cell: The cell you want to use as your goal.
To value: The value you want as your goal.
By changing cell: The cell you would like to change to reach your goal value.

With these three settings set, Excel will attempt to improve the value in the cell you set in By changing cell until the cell you set in Set cell reaches the value you determined in To value.

If all that sounds confusing, the examples below will give you a good idea of how Goal Seek works.

Goal Seek Example 1

For instance, let's say you have two cells (A & B) and a third cell that calculates the average of these two.

Now suppose you have a static value for A, and you wish to raise the average level by changing the value for B. By using Goal Seek, you can calculate what value of B will produce the average you want.

How to Use Goal Seek in Excel

In Excel, click cell C1.
In the formula bar, enter the following code:
```
=AVERAGE(A1:B1)
```
This formula will have Excel calculate the average of the values in cells A1 and B1 and output that in cell C1. You can make sure your formula works by inputting numbers in A1 and B1. C1 should show their average.
For this example, change the value of the A1 cell to 16.
Select cell C1, and then from the ribbon, go to the Data tab.
Under the Data tab, select What-If Analysis and then Goal Seek. This will bring up the Goal Seek window.
In the Goal Seek window, set the Set cell to C1. This will be your goal cell. Having highlighted C1 before opening the Goal Seek window will automatically set it in Set cell.
In To value, insert the goal value you want. For this example, we want our average to be 26, so the goal value will be 26 as well.
Finally, in the By changing cell, select the cell you want to change to reach the goal. In this example, this will be cell B2.
Click OK to have Goal Seek work its magic.

Once you click OK, a dialogue will appear informing you that Goal Seek has found a solution.

The value of cell A1 will change to the solution, and this will overwrite the previous data. It's a good idea to run Goal Seek on a copy of your datasheet to avoid losing your original data.

Goal Seek Example 2

Goal Seek can be useful when you utilize it in real-life scenarios. However, to use Goal Seek to its full potential, you need to have a proper formula in place, to begin with.

For this example, let's take a small banking case. Suppose that you have a bank account that gives you a 4% annual interest on the money you have in your account.

Using What-If Analysis and Goal Seek, you can calculate how much money you need to have in your account to get a certain amount of monthly interest payment.

For this example, let's say we want to get $350 every month from interest payments. Here's how you can calculate that:

In cell A1 type Balance.
In cell A2 type Annual Rate.
In cell A4 type Monthly Profit.
In cell B2 type 4%.
Select cell B4 and in the formula bar, enter the formula below:
```
=B1*B2/12
```
The monthly profit will equal the account balance multiplied by the annual rate and then divided by the number of months in a year, 12 months.
Go to the Data tab, click on What-If Analysis, and then select Goal Seek.
In the Goal Seek window, type B4 in Set cell.
Type 350 in the To value cell. (This is the monthly profit you want to get)
Type B1 in By changing cell. (This will change the balance to a value that will yield $350 monthly)
Click OK. The Goal Seek Status dialogue will pop up.

In the Goal Seeking Status dialog, you'll see that Goal Seeking has found a solution in cell B4. In cell B1, you can see the solution, which should be 105,000.

Goal Seek Requirements

With Goal Seek, you can see that it can change only one cell to reach the goal value. This means that Goal Seek cannot solve and reach a solution if it has more than one variable.

In Excel, you can still solve for more than one variable, but you'll need to use another tool called the Solver. You can read more about Excel's Solver by reading our article on How To Use Excel's Solver.

Goal Seek Limitations

A Goal Seek formula uses the trial-and-improvement process to reach the optimal value, which can pose some problems when the formula produces an undefined value. Here's how you can test it yourself:

In cell A1, type 3.
Select cell C1, then in the formula bar enter the formula below:
```
=1/(A1-5)
```
This formula will divide one by A1-5, and if A1-5 happens to be 0, the result will be an undefined value.
Go to the Data tab, click What-If Analysis and then select Goal Seek.
In Set cell, type C1.
In To value, type 1.
Finally, in By changing cell type A1. (This will change the A1 value to reach 1 in C1)
Click OK.

The Goal Seek Status dialogue box will tell you that it may not have found a solution, and you can double-check that the A1 value is way off.

Now, the solution to this Goal Seek would be to simply have 6 in A1. This will give 1/1, which equals 1. But at one point during the trial and improvement process, Excel tries 5 in A1, which gives 1/0, which is undefined, killing the process.

A way around this Goal Seek problem is to have a different starting value, one that can evade the undefined in the trial and improvement process.

As an example, if you change A1 to any number greater than 5 and then repeat the same steps to Goal Seek for 1, you should get the correct result.

Make the Calculations More Efficient With Goal Seek

What-If Analysis and Goal Seek can speed up your calculations with your formulas by a great measure. Learning the right formulas is another important step toward becoming an excel master.

Android Tricks 4 All

Saturday, April 3, 2021