How to use the Goal Seek and Solver tools to solve targeting problems

Loading...

From the course by University of Colorado Boulder

Excel/VBA for Creative Problem Solving, Part 1

276 ratings

University of Colorado Boulder

Excel/VBA for Creative Problem Solving, Part 1

276 ratings

Course 1 of 3 in the Specialization Excel/VBA for Creative Problem Solving

"Excel/VBA for Creative Problem Solving, Part 1" is aimed at learners who are seeking to augment, expand, optimize, and increase the efficiency of their Excel spreadsheet skills by tapping into the powerful programming, automation, and customization capabilities available with Visual Basic for Applications (VBA). This course is the first part of a three-part series and Specialization that focuses on the application of computing techniques in Excel/VBA to solve problems. In this course (Part 1), you will: 1) create macros to automate procedures in Excel; 2) define your own user-defined functions; 3) create basic subroutines to interface with the user; 4) learn the basic programming structures in VBA; and 5) automate Excel’s Goal Seek and Solver tools and use numerical techniques to create “live solutions” to solve targeting and optimization problems. New to computer programming? The extremely intuitive and visual nature of VBA lends itself nicely to teaching and learning - what a fun way to learn to code! No prior knowledge in programming nor advanced math skills are necessary yet seasoned programmers will pick up new and creative spreadsheet problem solving strategies. After you have learned the basics of VBA, each module will introduce foundational and broad problems inspired by situations that you might encounter in the real world. To pass each module, you'll need to pass a mastery quiz and complete a problem solving assignment. This course is unique in that the weekly assignments are completed in-application (i.e., on your own computer in Excel), providing you with valuable hands-on training.

From the lesson

(OPTIONAL) Numerical techniques and live solution strategies

Week 5 is OPTIONAL! Those who complete the Week 5 assessments (Quiz 5 and Assignment 5) will earn an Honors designation on their Course Certificate (see the first reading "WEEK 5 IS OPTIONAL" below). Week 5 delves into some important numerical techniques that can be used in Excel without the use of VBA. You will also explore ways in which the targeting tools in Excel (Goal Seek and Solver tools) can be automated in VBA. Finally, you'll explore some exciting "live solution" methods that have distinct advantages, especially for case studies where you have multiple scenarios that must be solved. Week 5 represents the most math-heavy module of either part of "Excel/VBA for Creative Problem Solving" but will provide you with some extremely useful tools that you can implement in solving a variety of real world problems. Even if you choose to skip/forego Week 5, you can always use this material as reference in the future!

Week 5 preview1:47

How to use the Goal Seek and Solver tools to solve targeting problems7:55

How to solve optimization problems using the Solver tool9:11

Fuel tank example and limitations of the Goal Seek and Solver Tools7:33

Automating the Goal Seek and Solver Tools8:29

Circular Calculations in Excel7:39

Implementing the bisection method in Excel8:59

Implementing a live solution of the bisection method to solve a case study8:55

Using the Golden Section search technique for optimization problems in Excel7:56

Live solution of the Golden Search technique for solving an optimization problem7:25

Solving a case study for friction factor using a circular calculation6:18

Implementing targeting and optimization algorithms in VBA subroutines8:45

Bisection method in a VBA function5:14

Preview of Part 2 of the course4:42

Meet the Instructors

Charlie Nuttelman
Instructor
Chemical and Biological Engineering

Okay, in this screencast,

I'm going to explain all about what targeting problems are.

Targeting problems are quite common in math and science and engineering fields.

So, a targeting problem is when you generally set up a spreadsheet,

and you've got an input cell, we might have any,

a value of six in our input cell,

and then we have an output cell.

I don't know, maybe we have an output cell of 27.2,

and somehow these are linked.

So, the input cell directly

affects the output cell through some sort of mathematical relationship.

And it doesn't have to be a simple formula,

it doesn't have to be a direct formula,

it can be through a bunch of different linked cells on the spreadsheet,

but in general, we have an X,

and we're going to call that our input,

and we have an output,

which we're going to say is a function of X,

so f of x.

Now, targeting problems are when you're trying to drive f of x equal to some value.

A lot of times, you're trying to drive f of x to zero and in that case,

you're solving for the root of an equation,

but it doesn't have to be zero.

You can also solve for a different target.

For example, you can set that f of x equal to a value such as five.

This is known as a targeting problem.

The target here is going to be the output cell,

and let's just say in this example,

we want the target to be zero and we want to change the input cell x.

Let me go through an example.

Here, we have just a mathematical equation,

and we wish to find the root of this equation,

and the root is the value that makes the left hand side equal to the right hand side,

which is the value of zero.

So, once we have found the root,

the left hand side is equal to zero.

There's a tool built into Excel that quite a few of you are

probably already aware of and that's the Goal Seek tool,

we can use this to solve non-linear equations.

The way Goal Seek works is you start with an input cell,

and you calculate the output,

and this is usually through some sort of mathematical relationship, and then you compare.

You compare the function value f of x to some target,

and a lot of times,

the target is zero, but the target does not have to be zero.

You compare to see how close those are,

and that's done using this tool called Goal Seek,

and when the f of x and the target are sufficiently close,

then the Goal Seek terminates this process.

However, if the f of x and the target are not close to one another,

then the Goal Seek tool adjusts x such that it changes the output f of x,

and the Goal Seek tool keeps iterating until the difference between

the function and the target value are minimal within some tolerance value.

In our example, we could go ahead and do this in Excel.

So, I want to show you how we can do this in Excel.

Importantly, we also need an initial estimate for x.

So, you need some sort of initial guess.

So over in Excel, I've set up my input cell, and I'm just guessing.

So, I'm just guessing it an initial guess of five and in my output,

then I can type in a formula for our function.

So, I've typed in the formula down here,

and when I press Enter,

we're calculating f of five because this is referencing cell C2,

this is referencing our initial guess.

And I'm not at my target yet because I want this target to be zero.

If I wanted to, I could manually change x to see what effect that has on the output,

and without too much difficulty,

you could narrow this down,

and we could find something that's quite reasonable.

So, it's not that hard,

but as you get more and more complex functions that depend upon different things,

then this becomes harder to do.

And it's a lot faster if you just automate it using the Goal Seek tool.

So, I'm going to use the Goal Seek tool.

We want to set cell C3 to a value of zero by changing cell C2.

So up here on the Data tab over in What-If Analysis,

I can click that, and there's this tool called Goal Seek.

And Goal Seek is just a pretty simple tool.

You can set any cell to a value by changing a different cell.

So, I'm going to set my target cell.

My target cell is going to be C3.

I want that to be a value of zero,

and I'm going to be changing cell C2.

And when I click OK,

it automatically churns through different scenarios and zeroes in on the solution.

So when X is 0.783,

then that satisfies this relationship down here.

The output is zero or close to,

and so we found the root of this equation.

Let's go through one more example using

estimates of rainfall, evaporation, water consumption.

The town engineer developed the following model of

the water volume in the reservoir as a function of time.

So, we've got this equation.

And what we're trying to do is,

how long will it take until the reservoir is empty?

So we want to solve for the time at which the volume is empty.

Now some of you might know some advanced math skills,

you might be able to do this by hand analytically.

Unfortunately, a lot of us forget

our basic math skills and this I think is actually quite hard to solve.

So, we use a computing tool.

This is perfect for using the Goal Seek tool in Excel.

So, we want to adjust T until the volume is equal to zero.

So, I've got this set up in Excel,

you always need an initial guess,

so I'm just going to say something like 50 days,

and now I'm going to plug in the equation for volume,

so here's my equation.

And when I press Enter, this is the volume in liters.

And again if you wanted to,

you could adjust this and just see what happens.

If I go to 200 days, it becomes negative, 100 days,

and so on, you could just manually do that or you can use the Goal Seek tool.

We're going to set cell B4 to a value of zero.

We want to figure out when there's going to be zero left.

It doesn't have to be zero, I could say

when is there going to be a million liters left or

a billion liters left by changing the time.

And when we do that, it cranks through, it finds a solution.

It says about 106.5 days until this reservoir will be empty.

We can also use,

there's a Solver tool over here on the Data tab,

on the data ribbon.

If that's not showing,

you can always go into File, Options, Add-ins,

and then you can go down here and click Go,

and you can click the Solver Add-in.

So, let's go ahead and run the Solver.

The Solver tool does the same thing.

Solver tool has a lot more stuff, we'll talk about,

it's more important and more valuable for doing optimization scenarios.

But we're going to set the objectives,

I'm going to set objective the volume equal to not a max

or a min but to a value of zero by changing variables cell,

that's going to be B3.

You noticed that in the Solver tool,

you can change multiple cells.

You can also add constraints,

which can be important.

So, I'm going to just do that.

We're adjusting the volume until it's equal to zero by changing the time.

And we crank through this and it comes up with the same solution.

Explore our Catalog

Join for free and get personalized recommendations, updates and offers.

Coursera provides universal access to the world’s best education, partnering with top universities and organizations to offer courses online.

© 2018 Coursera Inc. All rights reserved.

Download on the App Store

Get it on Google Play