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:
-
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.
-
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:
-
Excel Solver can calculate the values of numerous Decision Variables simultaneously for a problem that has multiple inputs.
-
Excel Solver has the capability to operate within constraints that are imposed on any of the variables in the computation.
-
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.
-
Excel Solver can work with many discontinuous formulas within the computation.
-
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!
The original text is the most powerful and the biggest factor https://infogram.com/untitled-poster-1hd12yxj9lj1w6k?live on the way to your success. Our specialists have a lot of effective and interesting ideas and know how to approach them from a different perspective.
ReplyDeleteWhen someone writes an article he/she keeps the thought of a user in his/her brain that how a user can understand it. Thus that’s why this article is outstanding. Thanks! 경마사이트
ReplyDeleteYou are so awesome! I don’t think I’ve read through anything
ReplyDeletelike that before. 카지노사이트
So nice to find somebody with unique
ReplyDeletethoughts on this subject. Really.. many thanks
for starting this up. 사설토토
Excellent read, Positive site, where did u come up with the information on this posting? I have read a few of the articles on your website now, and I really like your style. Thanks a million and please keep up the effective work, as a writer I am providing professional academic writing services for students.
ReplyDeleteThis is good news for those people who have no idea about their goal in life and they are searching for help sot that they may also be successful in their life. I think you have solved their all problems by providing them with online tips. Dissertation Help
ReplyDeleteToday it is very difficult to find a writing service that provide MBA dissertation help online quality according to the modern writing style. I remember, two days ago I was looking for on behalf of my friend, but I know how much I have to make the struggle to find the best service.
ReplyDeleteKevin Costner Yellowstone John Dutton Raw Leather Jacket
ReplyDeleteOur specialists are merely a call away to tailor our cyber security solutions to fit your requirements.
ReplyDeleteThis is a fantastic piece of writing that is both interesting and educational. I'm not sure why other subject matter experts hadn't thought of this before.
ReplyDeleteUse the NORMDIST function to test for normality. The output confirms that B3 is Normally distributed with a mean of 0 and a standard deviation of 1. Sometimes it takes work for students who hire someone to take my online exam from USA expert tutors.
ReplyDeleteOnce again thanks for sharing this article with us. It look you spend a lot of time or effort to make this amazing one. Now it's time to avail jupiter florida airport for more information.
ReplyDeleteGoogle Expeditions in VR presents an incredible educational frontier! It's akin to the innovation seen in Invisalign Dubai, reshaping experiences. VR makes learning immersive, transporting students to places they might never physically visit. With Google's vast resources and the potential for global accessibility, this technology revolutionizes education, making it engaging and boundless. The fusion of VR and education truly heralds a new era!
ReplyDelete