- [John] I'm going to describe several ways
that we could solve a nonlinear equation in Excel
if we have just one equation.
And one way is just to make a plot.
So the equation we're gonna use
is the Peng-Robertson equation of state
where we cannot explicitly solve for volume,
and in this case we're interested
in if we have temperature and we have pressure,
what is the volume?
But we can certainly make a plot
by putting in various values for volume
and calculating pressure and then determining a solution
for the pressure that we're interested in.
The other way is when we have just one equation
we can take advantage of sliders to quickly
move through solutions and look for ones
that satisfy the equation.
And then of course the third way is to use Excel solver,
which of course because perhaps the only solution
when we have multiple equations,
but here we're just looking at one equation.
So let's look at the spreadsheets.
You can see on the right here,
list the equations and the parameters,
and then here I generated values for volume and pressure.
Put in a value for volume, calculate the pressure
and make the plot that you see on the lower left.
Second, I've created sliders which are here
and I'll show how you do that.
That allows me to quickly vary the volume
to minimize the equation.
So this equation is just putting
everything on the right side of the equation of state,
everything on the right side, and so that should equal zero.
And so we can vary the volume to get that to zero.
And then the third way is to vary the volume
to get that zero using solver.
So let's look at the spreadsheet
and see how we do each of these things.
So here's the spreadsheet and we're interested in the volume
for temperature 250 Kelvin, the pressure 0.093 megapascals.
And so if we look at the plot and what I've done
is drawn this green line that's approximately 0.093
and then we can read off.
And so we first noticed there's three solutions.
The lowest volume and the highest volume
can correspond to realistic physical volumes.
The middle volume has no physical meaning.
Which one is the volume for our system
depends on additional criteria
that we're not going to worry about here,
but we can essentially read off these three values.
And here are the values, that 41.54
'cause we can't read that actually from this plot,
but we can approximately read 41 and 120
and 21,000 or so just from the plot.
And so that's a quick way, one.
Making a plot allows us to tell how many solutions exist
and that's valuable information in general.
Here we of course, knowing his cubic equation of state
would already know three solutions are possible.
And this can of course only applied when we have,
perhaps with two equations we can still apply it
and get solutions, but it becomes less useful.
The second way is to use these sliders.
So if you're not familiar with sliders,
if you go to file options and under customize ribbon,
you see an option for developer.
If you select that, then the developer,
so let's put one over here if she can see how to do it,
the developer allows you to insert
in this little slider button.
And so I can put one here and then I can format that
by left clicking on it and it comes up with format.
Then I can select the range,
how much a change occurs when I move the slider,
and which cell I'm linking it to.
Well, I've done that already for volume
and I made two sliders just to make life a little easier.
This slider changes the volume relatively quickly.
This changes it in smaller steps.
And so I can step through, for example, this way
and this is steps of one when I'm close
to the value I'm interested in.
So, I put in a value of V of 30 just to demonstrate.
Notice F of V is negative.
And as I increase value V with the slider
increments of one, now it became positive.
So somewhere below 42 is one of the solutions.
And then I can take bigger steps
and this positive number is getting smaller
and again, by stepping slow, you can see what's happening.
Notice it's now negative.
So I went through a second solution, that's the one at 123.
And then the other one of course is much higher value.
So less jump up since we know the answer.
We go in bigger steps.
So right now we're at 11,800.
It's negative and as I keep stepping up,
it's become small, but it's very slowly changing,
but it's indeed changing.
If I keep doing this, it'll.
So, notice it's very, very small and then now it's positive.
So again, we passed through the third solution.
This approach particularly we have one solution
can be a way to check and we can move away the solution,
make sure it's a good one.
The third of course is, and let me demonstrate
by putting back like a low value for volume,
and that's to use solver.
And here we see that what happens is interesting
in that where we pick our starting point
determines which solution we're going to approach.
Well in general of course, if we are using solver,
we don't know what the plot looks like,
we don't necessarily know how many solutions.
But if I put it in a value of 10 is my guess,
and I go to data and solver and I've already put in
that bunch of change the volume to get this cell
to a value of zero and I push solve,
you'll see it can't find a feasible solution.
And indeed between one and 25,
I didn't try and get more detail on that.
Any value I pick, it does not find a solution.
If I pick a value of 30 and again use the solver,
now it converges to the solution.
It converges to the the smallest volume.
And indeed between 30 and 50 is my initial guess,
it converges to that solution.
If I now put in a guess of 55, and again use solver,
it converges to the middle solution 123.
And so between 55 and 275 is my initial guess,
it'll converge that middle solution.
Now if I put in a value of 280 as my initial guess
and let's just try one more time to use solver,
again it converges, but now it converges
to the high volume of 22,158.
So clearly, and I can put values between 280 and 500,000
as my guess, and it'll converge to the 22,158 value.
So, it's important to point out that we have to be careful.
Our initial guess affects whether we can get a solution
and which solution we approach.
And we have to keep that in mind when we're using solver.
But all of these techniques allow us
to solve a non-linear equation relatively easily.