Circular Calculations in Excel

Loading...

From the course by University of Colorado Boulder

Excel/VBA for Creative Problem Solving, Part 1

285 ratings

University of Colorado Boulder

Excel/VBA for Creative Problem Solving, Part 1

285 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

In this screencast, I'm going to show you how to

solve a special case of targeting problems,

and that is a circular calculation.

So I've got an example here,

x minus one divided by sine of x equals zero.

And I've already shown in

some previous screencasts how you can solve this using the Goal Seek tool.

So let's do that real quick.

So I've got my initial guess here of one.

I put a formula in here.

This is our f of x equals zero,

in terms of my x.

So x is our initial guess.

I go up here to Data,

What if analysis, Goal Seek,

I'm going to set cell B3 to a value of zero by changing cell B2 which is x,

and it goes through and it very quickly finds our solution to this equation.

So we can easily solve f of x equals zero type problems in Excel.

There's also some situations where you can rearrange

your equation into an x equals f of x type of a problem.

So as you see here, we've arranged this.

I've added the one over sine x term to both sides,

and we end up with x equals a function of itself.

So whenever you can come up with x equals f of x type of problem,

you should think about using what I'm going to show you in this screencast,

and that is to use a circular calculation.

So we rearrange into an x equals f of x form.

That's in contrast to the f of x equals zero type problem that we

use to solve using the Goal Seek and Solver tools.

This can be solved using something known as a circular calculation also

known as iterative solving or iteration or fixpoint iteration.

We can also solve something like the problem shown here,

where we can rearrange to another form of

the equation which is an x equals f of x problem.

We have an input,

that's our x, so we guess an x.

We have a calculation involving one or many formulas,

so we can have linked cells in the spreadsheet, three different formulas.

But the end result is some sort of output f of x.

If we're trying to solve the x equals f of x problem,

what we do is we compare x with f of x,

because if the input equals the output,

then we know that we have found the solution to the x equals f of x problem.

So we compare the output with the input and we keep

going until the difference between the input and output is negligible.

For the solution to be correct or approximately correct,

the input x going into the calculation has to be equal

to the output f of x coming out of the calculation.

So we're going to go through a simple example,

the x equals one over sine of x.

We're going to guess an x.

We're going to start with an initial guess,

then what we do is we calculate the right hand side of this.

We calculate one divided by the sine of our estimate and that will give us an answer,

and we compare those two.

If they're not approximately equal to each other,

then what we do is we take the output, which is going to be x,

and we're going to put that back into the x in the argument of the sine function.

So we start this with an initial guess,

I'm going to guess one,

and then we calculate f of x,

which is just simply one divided by the sine of x, which is A3.

Now, those are not equal to each other,

so we haven't satisfied the solution to this x equals f of x problem.

What we do next is the input to the next round of iteration,

is to take the output which was f of x and plug that into our x here in f of x.

So the output f of x becomes the x in the new round of iteration.

So in the next round of iteration,

the new x is just equal to the output of the previous round.

So that's equal to f of x.

Next, we can drag this formula down,

and this is just one divide by the sine of 1.88.

So in essence what we're doing is we keep taking the output of the function,

which is f of x,

and use that as the input x into the function and we keep iterating.

So I've set this up,

I can drag this down now a couple of rows,

and we're converging on a solution.

As we do that a couple of times,

this is like seven or eight rounds of iteration,

nine rounds of iteration,

we're seeing that we're converging on a solution of about 1.113 or 4.

So this is how iterative solving works.

And it's really efficient especially in Excel.

It's a live solution as we'll show in subsequent screencasts.

Now, there's another way to solve this,

and that's by enabling iterative calculations in Excel.

So I'm going to start with an initial guess of x equals one.

I'm going to put in here,

this is one divided by sine of x,

similar to what we did earlier but now the new x is going to be equal to the old f of x,

the output of the function.

So check out what I'm going to do here.

So the new x is equal to the old f of x, the output.

So the new input is equal to the output.

So I'm just going to put an equal sign and I'm going to click here.

And when press Enter,

it's going to give me a warning because this is known as a circular calculation,

which I'm actually trying to do.

I'm forcing Excel to do it circular calculation but it's going a problem.

To circumvent this, we want this to be a circular calculation,

you can go up here to File,

Options, Formula tab and Enable iterative calculations.

And let's just make sure we're doing 100 iterations so when we press Enter,

when I enable this, it's going to go through 100 iterations and converge on a solution.

That's exactly what it did,

it went through 100 iterations.

I don't think it actually went through 100 iterations there,

but that's the maximum.

It goes until it reaches some sort of tolerance,

which you can set back there in that menu option.

Now sometimes iterative solving doesn't work.

So let me show you what happens in this example.

So I typed in my function here with f of x,

which is a function of cell A3,

and I get my f of x.

Now I just put a pointer to a formula.

The new x is going to be the old f of x and I can drag this down.

So now I can just drag this down and you notice that it doesn't converge.

And we can look at different values of starting numbers.

So two, it's not converging.

Some cases, such as this one,

it actually explodes. It blows up here.

You see that we very quickly go into something

to the ninth and then something to the 17th.

Sometimes iterative solving doesn't work.

In that case, what we need to do is rearrange

our function such that we're solving for a different x.

So what I've done here is I've solved for this x,

in the numerator here on the right hand side,

in terms of the other xs.

So I've isolated an x,

and I have a new x equals f of x.

And I put the equation in here,

the new x is the old f of x,

and it looks like we are converging on something.

We're getting closer to an answer,

and I could go more iterations if I wanted to.

So the solution to this is about 3.04 or 3.05.

Again, iterative solving is very efficient in Excel,

and we can use it for live solutions which I'm going to

show in a subsequent screencast. Thanks for watching.

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