Automating the Goal Seek and Solver Tools

Loading...

En provenance du cours de University of Colorado Boulder

Excel/VBA for Creative Problem Solving, Part 1

340 notes

University of Colorado Boulder

Excel/VBA for Creative Problem Solving, Part 1

340 notes

Cours 1 sur 3 dans 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.

À partir de la leçon

(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

Rencontrer les enseignants

Charlie Nuttelman
Instructor
Chemical and Biological Engineering

All right. We're going to continue to address some of the issues

that arise when we're trying to use the Goal Seek and Solver tool,

especially with respect to doing case studies,

where we have a vector of inputs.

We're going to continue talking about this gas tank problem.

In the last screencast,

we set up and solved for a couple of different volumes.

At what distance from the end of that dipstick do we need to put the markings for 100,

200, and 300 gallons?

Now, the problem that arose is we wanted to put markings

on the dipstick all the way up to the total volume of this tank,

which is just under 3400.

And in order to do that,

we still have to do the Solver or Goal Seek tools another 29 times.

There's a better way to do this,

and I'm going to show you in this screencast how we can automate this process in VBA,

so we can automate the Goal Seek and the Solver tools.

So, first of all, I'm just going to go ahead and clear this,

and we're going to set this up to solve using Goal Seek,

and we're going to record a macro while we do this.

I've got my initial guess.

I'll just reset to 10 up here,

and we are ready to go.

So I'm going to go up here to the developer tab,

record macro, and I'm just going to name this Macro1.

It's recording, and I'm going to just do the Goal Seek.

We're going to set cell B13 to a value,

and I'm just going to put in a number here.

We're going to change that in our recording because

we want to make that automatically changed.

By changing so, we're going to change cell B6.

So this is how we did it in the previous screencast,

and I'm just recording a macro for how it does this.

I'm going to go ahead and click okay,

and then I'm going to go to the developer tab and stop recording.

So let's take a look at what was recorded.

We've got three lines here.

These don't look to be very useful.

I don't think those are needed.

But the important line is this one that has the Goal Seek.

So I'm going to go ahead and copy that,

and I've already started a sub called Goal Seek.

I'm going to paste this in there.

Now, what we're trying to do is we want to put

the values here that are in this volume column into here instead of this 100.

The way I'm going to do that is,

well, so we're going to put this into a for loop.

But before we do the for loop,

we're going to make sure that cell B16 is selected,

and that's always going to be the active cell.

We're never going to change the active cell from there.

We're just going to offset and we're going to use different values from the active cell.

So I've dimmed i because we're going to have a for loop.

We're going to be iterating through.

We have 33 rows.

If we wanted to, we could somehow count the number rows,

but I'm just going to put 33 because I know that we have 33 rows.

We're then going to select B16 just to make sure that that's always selected,

and that's going to be the active cell that we're going to base everything off of.

So now I've added this for loop for i equal 1 to 33. We're going to go through.

We're going to do a Goal Seek on cell B13.

So that's what we're going to put the individual volumes here in our column.

And instead of 100, We're going to use active cell offset.

So instead of 100,

I put active cell that offset( i -1, 0).

The reason we do i minus 1,

for the first row,

i is going to be equal to 1, and we don't want to offset any rows.

For the second row, we're only offsetting one row.

So we're offsetting always i minus 1 and 0 columns.

Now, at the end of each iteration,

we need to somehow take the value that is in B6,

that's going to be the result,

and we want to put that into the adjacent cell to the active cell in the H column here.

We can do that by saying activecell offset( i - 1). Again, we're using( i -1, 1 ).

That means the offset one column over,

which is going to be the depth column equals at the end of each iteration,

we want to take whatever's in cell B6 and place that into the cell.

So now, i is going to be bumped to two.

This is still going to be active cell.

B16 is not going to be changed,

but we're going to take into the Goal Seek.

We're going to use 200.

And we want to put that cell into one,

the active cell offset one row and one column.

So I think we're ready to go.

I'm just going to make sure my initial guess seems reasonable,

and I'm going to go in here and press F8.

So B16 was selected, i is equal to 1.

We do the Goal Seek.

We get a result of 5.4,

and now, we're going to put that 5.4 into that cell.

And we keep going. Now, i is equal to 2.

We do this again.

We get 8.5. Another 8.5 needs to be placed there. And looks like it's working.

So what I'm going to do is I'm going to just kind of run the entire rest of the program.

I'm pretty sure it's working.

So I'm going to press continue,

and it goes through and calculates

the depths that correspond to those different volumes. This is pretty slick.

So what we've done is we just automate the Goal Seek.

The one problem now though is if we change the length,

it doesn't automatically update all those heights.

In order to update that,

and this can be confusing especially if you're giving this to a client or a coworker,

is whenever they change something on the spreadsheet,

they have to rerun that Goal Seek macro,

and you see that it changed those values.

So it's not a live solution.

So let me show you now how we can do this using the Solver tool.

So I'm going to go up here to the developer tab.

I'm going to start recording,

and then I'm going to go to the data tab, Solver,

and it's remembered my previous scenario so there's not much I need to do there.

And I'm going to go ahead and press solve. It clicks through.

It finds a solution, and now,

I'm going to go to the developer tab and do stop recording.

Now, you notice that the code that it recorded

is basically repeating two pairs of lines here.

I don't know why it does that, but the most important thing we need

are just these three lines so I'm going to go ahead and copy that.

I'm going to go ahead and I've already created a sub-Solver tool,

and we're going to then make this look a lot like the Goal Seek.

So it's similar. We're going to select cell B16.

We're going to iterate through all of the rows in our volume input vector.

The one thing we need to change,

instead of targeting to a value of 100, we're going do what we did up here.

We're going to take the active cell offset value.

It's pretty important from what I've experienced that you use the value property.

So make sure you put the value property on there.

So I think we're ready to go.

So let's go ahead and run this.

So we press F8.

It selects cell B16 over here.

Then I press F8 again,

and we're going to solve that scenario using 100.

That says we found a solution,

and then we're going to take that to 5.4 here.

And we're going to place it into the adjacent cell,

just like we did with the Goal Seek.

We do this again,

and it brings up this box again and we click okay.

So it looks like it's working.

So we're going to place that into the adjacent cell here.

The one problem you notice is every time we go through this iteration,

it's coming up with that Solver box where you have to click okay.

You can bypass that.

By after Solver's solve,

you can just type True.

Now, when we do this,

it doesn't bring up that box.

It just kind of moves along. And then we can keep going.

I'm pretty certain this is working.

So let's just go ahead and resume.

I'm going to press that play,

and it goes through here and the end result is looking good,

where we got all the different depths corresponding to the volumes.

So that's how you can set this up in Solver.

So that's how you can set up

the Solver to be automated just like we did with the Goal Seek.

Now, there's still the problem of this is not a live solution.

If we change the length or the radius,

as I showed previously for the Goal Seek,

it's not going to automatically update the heights.

And so we're going to work with making

live solutions in subsequent screencast. Thanks for watching.

Explorer notre catalogue

Rejoignez-nous gratuitement et obtenez des recommendations, des mises à jour et des offres personnalisées.

Coursera propose un accès universel à la meilleure formation au monde, en partenariat avec des universités et des organisations du plus haut niveau, pour proposer des cours en ligne.

© 2018 Coursera Inc. Tous droits réservés.

Télécharger dans l'App Store

Disponible sur Google Play