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.
Charlie NuttelmanInstructor
