JOHN: In this screencast,
I'm going to demonstrate using an Excel spreadsheet
to determine kinetic parameters
for using nonlinear regression
and we're solving numerically,
the ordinary differential equation
for mass balance for a plug-flow reactor.
And in previous screencasts,
describe the equations being used
and show the setup of the spreadsheet
but in order to keep these screencasts to reasonable length,
we've broken them into several parts.
Here, I'm going to look at the actual spreadsheet
and some of the terms,
so lemme pause and bring up the spreadsheet.
So we're looking at the spreadsheet
and a couple things to point out,
if I select the cell for KA
and we look in the upper left hand corner,
we see we've named that cell KA
and we've named the cell below it K,
much easier in our calculations if we name the cells,
so likewise over here,
name, pressure and temperature and so forth.
So the Runga-Kutta terms.
I explain how we get those terms
if we look at one cell,
we can see the calculation where we're using
the volumetric flow rate, vo
and we have the K terms.
and then we're using the concentrations,
so I11 is the concentration here,
that's the inlet concentration, the reactor.
We're always using concentration
from the previous step in the next calculation.
So if we go down to here to calculate K1,
we're using I12,
which is the concentration we calculated
in the previous step.
So we step through, there's 400 steps
to get to a volume of 20 cubic centimeters.
Now, the valuable part is over here with the table.
So what I've done is first,
type in the values where the measurements were made
for volumetric flow rate
and the experimental values.
So these were generated using a computer
but they are generated and the no is added.
So these are experimental values for these flow rates
and then the critical thing is now,
in this column you'll notice, if you look up here,
it says table D8.
So what that means is,
D8 is the volumetric flow rate here.
We're gonna change the volume metric flow rate
because what I've done is,
I typed in this volumetric flow rate
and then to create a table,
I had to select these values,
so lemme step back and show you.
So before these values are calculated,
I select all of these cells
and the important thing
is, here, this is equal to, if we look up,
it's equal to I411.
That's the outlet concentration from the reactor
and when we finish that numerical integration
it's the last value in this I column,
exit the reactor.
Way the cell works, this cell here determines
what values we're gonna substitute in here.
So when I select this and then I go to data
and I go to What-If Analysis
and I go to data table.
In this case all I have is a column input cell
and so what I would do is select these values
and if I hit return,
I don't want to actually change the cell,
then we would...
And then I would hit okay, I'm sorry,
then it would actually change,
so let's not actually do that.
But the way the table works,
it uses this value for the calculation
and it calculates this particular cell, I411,
here for flow rate of one, here for flow rate of 2.4.
And so, if I were to change K up here,
lemme just change this to say 0.2.
It's gonna actually do that numerical integration
for each of these volumetric flow rates
and input the values in the table.
So since I don't want to make that change permanent,
I'm gonna go back to where we were.
So over here now,
the fact that we've generated what we expect
the concentrations to be leaving
the reactor for the reactant,
for whatever K values we put it in here
and of course, when we start,
we put some values that hopefully are reasonable
and we're gonna iterate to them.
And so we wanna compare these calculated values
from the ordinary differential equation solution,
to our experimental values.
We take the difference,
so if you look at this cell,
it's the difference squared
and we sum up those values,
this is what we're minimizing.
So then what we would do is go to data, solver
and I've already selected it
to minimize all the variables non-negative.
This is the cell with the sum of the squares.
These are the cells for KA and K,
if I hit solve, it'll go through on solve again
and it'll then generate the best values
which of course, then changes these values,
assuming I started with some numbers other than the answer.
And then I can also calculate the residuals here,
it's just a difference between the experimental
and then calculate.
If we look at the cell up here,
it is residual squared and residuals.
And so then what I can do
and I have some notations explained in the spreadsheet,
I can make a plot, just insert a plot
so I won't go through how to do that,
I'm assuming that that's something everyone is aware of.
I can make these plots
to better characterize how well our fit is
and if it's a random distribution.
So the spreadsheet, which is available
on the LearnChemE.com website,
also has the formula,
so you can look
and see exactly what formula is used
to calculate the K values at each step.
And then it shows the data table.
Now it also shows
how we go about doing the confidence limits
but that takes some time,
so I'm gonna do that in another screencast.