# Simplifying Goal Seek in Excel

Goal Seek is one of the tools in the What-If Analysis category, which is located under the Data tab in the ribbon. Goal Seek calculates the value of a single input variable of a computation that will produce a specified value in the outcome.

An input variable that is changed in order to produce a specified outcome is called a Decision Variable. The output variable is called the Objective. Goal Seek calculates the value of a single Decision Variable that will produce a specified value of the Objective.

Goal Seek is a very limited tool for the following reasons:

1. Goal Seek can only calculate the value of a single Decision Variable. This means that all other inputs to the computation must remain constant while only the single Decision Variable can be changed. No other input can be varied while Goal Seek is performing its calculations.

2. Goal Seek cannot find a solution if any part of the computation contains a discontinuous formula.

The Excel Solver is the preferred tool when attempting to calculate the value of a Decision Variable that will produce a specified result in the Objective for the following reasons:

1. Excel Solver can calculate the values of numerous Decision Variables simultaneously for a problem that has multiple inputs.

2. Excel Solver has the capability to operate within constraints that are imposed on any of the variables in the computation.

3. Excel Solver can calculate the value of Decision Variables that can maximize or minimize an Objective in addition to merely calculating the value of Decision Variables that produces an exact value in the Objective.

4. Excel Solver can work with many discontinuous formulas within the computation.

5. Excel Solver can be set up just as quickly as Goal Seek to solve any problem simple enough that can be solved with Goal Seek.

The brief example problem in this article will therefore be solved with both Goal Seek and the Excel Solver to compare the basic operation of the two. The problem to be solved is the following:

(Click On Image To See a Larger Version)

The purpose of this exercise is to calculate the value on the Decision Variable (cell B2, which currently contains the value of 4) that will cause the Objective (cell B4, which currently contains the value 625) to assume the value of 3,125. The solution is reached when the Decision Variable equals 5 because 55 = 3,125. This simple problem will be solved with both Goal Seek and with the Excel Solver.

## Solving a Single-Decision-Variable Problem With Goal Seek

Goal Seek is one of the tools in the What-If Analysis category under the Data tab in the ribbon. Selecting Goal Seek brings up the Goal Seek Dialogue box shown as follows:

(Click On Image To See a Larger Version)

The Goal Seek Dialogue is very simply to configure because Goal Seek changes the value of a single Decision Variable that will produce a specific value in the Objective cell without allowing any constraints to be imposed on any variables in the computation. The completed dialogue box for this problem is shown below:

(Click On Image To See a Larger Version)

Clicking OK produces the following solution. Goal Seek occasionally requires computations to be rounded off.

(Click On Image To See a Larger Version)

## Solving a Single-Decision-Variable Problem With Excel Solver

The Excel Solver require just about the same amount of work to set up and solve this problem. The Excel Solver is an add-in that must be activated on a one-time basis before it appears in the ribbon and is available for use. Most versions of Excel ship with the Solver as an add-in that needs to be activated, but some versions of Excel do not.

Assuming that the Solver has activated, the Solver will appear under the Data tab in the ribbon as follows:

(Click On Image To See a Larger Version)

Selecting Solver will produce the following empty dialogue box.

(Click On Image To See a Larger Version)

(Click On Image To See a Larger Version)

For the problem shown here, The Solver dialogue box would be completed as follows:

(Click On Image To See a Larger Version)

Note that the Excel Solver has the capability to solve for multiple Decision Variables simultaneously and also has the ability to operate within constraints that the user can impose. The Solver method used is the GRG Nonlinear method. This solving method is the default for Solver and can be used to solve linear and nonlinear equations that are continuous. The details of the different Solver methods are discussed in other articles in this blog that focus specifically on that topic.

Selecting Solve produces the following output:

(Click On Image To See a Larger Version)

The Solver creates 3 reports related to different aspects of the computation of its solution.

To sum up, it is better to use the Excel Solver to perform any calculation that can be performed with Goal Seek. The Solver can be set up just about as quickly as Goal Seek but has so much more capability and available output information.

Excel Master Series Blog Directory

Click Here To See a List Of All

Statistical Topics And Articles In

This Blog

You Will Become an Excel Statistical Master!

#### 3 comments:

1. It is a good idea to increase the volume of products and services.There are many strategies for marketing the products. Behind each, the main moto is to satisfy clients with what ever they want and what ever they expect.Appreciating the dedication you put into your blog and in depth information you provide. It's awesome to come across a blog every once in a while that isn't the same outdated rehashed material.I believe there are many other people who are interested in them just like me! How long does it take to complete this article? I hope you continue to have such quality articles to share with everyone! I believe a lot of people will be surprised to read this article!If you are in need for online writing assistance for an intricate thesis topic, then avail cheap essay writing service and save your time to relax and do your studies properly.

2. Dentistry Research Paper Writing Services have come up with Dentistry Writing Services for dentistry coursework writing service students in order for them to score straight A’s in their dentistry paper writing services.