Tuesday, March 10, 2015

Simplifying Goal Seek in Excel

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:

Goal Seek in Excel - Initial Problem Data
(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:

Goal Seek in Excel - Selecting Goal Seek
(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:

Goal Seek in Excel - Goal Seek Dialogue Box
(Click On Image To See a Larger Version)

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

Goal Seek in Excel - Goal Seek Final Answer
(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:

Goal Seek in Excel - Select Solver
(Click On Image To See a Larger Version)

Selecting Solver will produce the following empty dialogue box.

Goal Seek in Excel - Empty Solver Dialogue Box
(Click On Image To See a Larger Version)

Goal Seek in Excel - Initial Problem Data
(Click On Image To See a Larger Version)

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

Goal Seek in Excel - Solver Dialogue Box
(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:

Goal Seek in Excel - Solver Solution
(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!

Posted by at

19 comments:

  1. GoldyyMay 25, 2021 at 4:11 AM

    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.

    ReplyDelete
  2. Racesite ProSeptember 13, 2021 at 12:03 AM

    When 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! 경마사이트

    ReplyDelete
  3. Oncasinosite NetSeptember 13, 2021 at 12:03 AM

    You are so awesome! I don’t think I’ve read through anything
    like that before. 카지노사이트

    ReplyDelete
  4. Totopick ProSeptember 13, 2021 at 12:04 AM

    So nice to find somebody with unique
    thoughts on this subject. Really.. many thanks
    for starting this up. 사설토토

    ReplyDelete
  5. MichelAnthonyNovember 10, 2021 at 1:15 AM

    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.

    ReplyDelete
  6. Roman DavisDecember 6, 2021 at 4:05 AM

    This 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

    ReplyDelete
  7. joaquinkylianFebruary 17, 2022 at 1:07 AM

    Today 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.

    ReplyDelete
  8. Kevin Costner Yellowstone John Dutton Raw Leather JacketJuly 16, 2022 at 6:15 AM

    Kevin Costner Yellowstone John Dutton Raw Leather Jacket

    ReplyDelete
  9. web application penetration testingSeptember 8, 2022 at 7:30 AM

    Our specialists are merely a call away to tailor our cyber security solutions to fit your requirements.

    ReplyDelete
  10. assignment writerOctober 13, 2022 at 4:40 AM

    This 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.

    ReplyDelete
  11. Maxwell LordNovember 22, 2022 at 3:13 AM

    Use 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.

    ReplyDelete
  12. TOMAugust 7, 2023 at 11:39 PM

    Once 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.

    ReplyDelete
  13. ZoeNovember 28, 2023 at 2:07 AM

    Google 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
  14. Elise HiltonDecember 28, 2023 at 5:24 AM

    For anyone attempting to manage the complexities of data analysis, this article on Simplifying Goal Seek in Excel is a game-changer. It is much simpler to understand the idea thanks to the step-by-step explanation and concise examples. Bravo to the author for making what might be a difficult process easier! identify sources of support for planning and reviewing own development.. The article makes a hint at this by highlighting how Goal Seek can be used practically in a variety of situations. Seeking mentoring, taking online classes, and working with peers are, in my opinion, excellent sources of help.

    ReplyDelete
  15. Online Exam ExpertsFebruary 12, 2024 at 1:43 AM

    Informative and valuable content!

    ReplyDelete
  16. Ayan UmairMarch 22, 2024 at 11:48 AM

    "I love your writing style, it's so engaging and easy to follow. Can't wait to read more from you!"
    take my online class

    ReplyDelete
  17. hire someone to take my online examApril 2, 2024 at 4:38 AM

    I was truly impressed by the depth of knowledge demonstrated in this article. It's evident that the writer is passionate about the topic and has a strong command of the subject matter."

    ReplyDelete
  18. The Pie MatrixJune 14, 2024 at 3:48 AM

    At Dobsonian Telescopes India, we take pride in providing top-notch telescopes that are tailored to meet the needs of Indian astronomy enthusiasts. Our telescopes are equipped with high-quality optics and sturdy mountings, ensuring stable and precise observations. Whether you are interested in observing the moon, planets, or deep-sky objects like galaxies and nebulae, our telescopes will provide you with an immersive and unforgettable experience.

    In addition to offering a diverse range of telescopes, we also provide comprehensive customer support and guidance to help you make the most of your telescope. Our team of experts is always ready to assist you in choosing the right telescope for your needs and answering any questions you may have. With dobsonian telescopes India, you can embark on a fascinating journey through the cosmos and discover the beauty and mysteries of the universe from the comfort of your own.

    ReplyDelete
  19. Shaun PollockOctober 2, 2024 at 6:26 AM

    "Thank you for sharing this wonderful insight! Your perspective is refreshing and really encourages thought. I found the way you articulated your points particularly engaging and relatable. It’s clear you’ve put a lot of effort into this, and it’s much appreciated. I look forward to seeing more of your work—keep it coming!" E Book Writing Service

    ReplyDelete

Subscribe to: Post Comments (Atom)