In a separate screen cast,
I explained how we use
Solver to determine parameters.
We're doing nonlinear regression
in this case to determine
the activation energy and a pre exponential factor
for kinetics rate expression.
Here I want to just concentrate on
how we use Excel to get
95% confidence intervals.
And so, in order to do that,
let's point out a couple of things.
We're fitting the data to the form
K zero and exponential E activation energy,
R dash constant
one over absolute temperature
minus one over T zero.
So, R is the gas constant,
in this case we'll use
eight point three one four joules per mole Kelvin
and T zero is the
temperature halfway in the range
between 700 and 840,
so that happens to be 770 Kelvin.
And K zero then
is the rate constant evaluated at T zero.
We're gonna need a couple partial derivatives
and expressions that we're gonna
use to determine these confidence intervals.
One of them is the
partial of the rate constant
with respect to K zero.
Well, it's fairly straight forward,
just this exponential.
And the other partial
we're going to need is
the partial of
K with respect to E.
So we're taking partials
with respect to
the two parameters that we determine
by nonlinear regression.
And so this means we have an exponential
[mumbles] exponential,
and then we have
the derivative of the term
in the exponential
with respect E
and that term is then minus one over R
one over T minus one over T zero.
Let me close just to rearrange that a little.
So when you use these two partials
and what I'm gonna show you now is the spreadsheet
explained in various terms.
Keep in mind,
I've already used this spreadsheet
in a separate screen cast to calculate
with solver K zero and E that best fit the data.
And now I'm just gonna explain the terms
for the confidence interval.
Well, the first part of the spreadsheet
is the part where we put in the data
and then we use solver
to calculate K zero and E.
We've also calculated value so we can make plots in Excel.
So, let's look at the part
where we're determining now
the confidence intervals.
First thing we do is create this matrix.
Each value in this
row is part of the matrix
is the partial of K respect to K zero,
and we just calculate that.
Likewise here is the partial K respect to E,
we also just calculate that.
And then, the important thing is that we make this a matrix.
And so, in Excel what that means is we select these cells,
and we go into the section of Excel where we can name cells
and we name that cell X.
Next, we're going to create a matrix
by taking the transpose of the X matrix multiplied by
the inverse of that matrix.
And the way we'll end up doing that
in Excel the commands we're gonna use,
let me pause and write them down.
So, what we have is
this is the matrix inverse
matrix multiply, the transpose of X times X.
And the way we create this is we select
these four cells,
we type in this formula with of course, an equal sign,
and then we select Control + Shift + Enter
because it's the right calculation,
and then we get this matrix.
So then we're almost finished.
We need a few more things,
we're interested in 95% confidence limits.
So this alpha is 5%,
this T value from T distribution
again is a formula in Excel.
So here's the formula to get the T value where alpha,
we define this cell as alpha.
Now we calculate this standard error squared,
So it's this term here squared,
which is the same as the sum of the squares
that we minimized divided by degrees of freedom.
Degrees of freedom is number of data points,
minus number parameters,
that's four in this case.
So, this term S K zero,
it's the square root of S C squared
times the one element of this matrix.
So in this case,
zero point one six zero five.
This term, again the square root
S E squared times the two element in the matrix.
Once we have those we can calculate,
we've done that down here,
the confidence intervals.
So this confidence interval is
the T value
times S K zero,
this is the T value times S activation.
So, I've not explained why we do this.
Our interest here is the mechanics
of how we carry out these calculations
in order to keep this at a reasonable length.
I've also written the activation energy in Kilojoules
instead of joules,
and then I've calculated the pre exponential factor,
and then the 95% confidence limits pre exponential factor.
And the pre exponential factor was just
K zero times E over R T zero.
So, on another screencast right here
look at the Excel program
in carrying out these calculations.