INSTRUCTOR: In this screen cast,
I'm going to describe how we obtain
the R squared valuing confidence limits
for parameter values that we turned by Nonlinear regression.
We use Nonlinear regression in Excel using the Excel solver
in a none of the screencast, here We're concentrating
on how we got the values for the confidence limits
and how we got the R squared values.
We're gonna go to a spreadsheet, same spreadsheet
that we used in the other screencasts
and we're now going to continue to add to it,
to get these values.
Well, here's the spreadsheet that we use
to get the parameter fusing nonlenear regression
in another spreadsheet,
Now what I wanted to do
is calculate the R squared value first.
So let me show how we do that.
One of the things I'm going to do is label,
so this yellow cell was the sum of squares of the difference
between the calculated value and the experimental value
and so, just so we remember that I'm going to label this
and I'm also going to name this cell
up in the upper left hand
column SS to make it easier to use in calculations
the other thing we need
is to calculate the sum of the squares
of the difference between fair mental value
and the mean value that quantity squared.
And so we want to put that sum here,
and so I'm just going to copy the cell here.
So this then is this sum
and I'm going to label put a label over here
just so we make it easier to remember
and then I'm going to likewise name this cell
SS mean, now I'm ready to calculate the R squared value
and it's simply equal to one minus SS
divided by SS mean.
So I, you know, value close to one,
expect if it's a good fit, let me label this also.
So now let me calculate
some other values that we're going to need,
in this the number of data points, which in this case is 10
and here is the number of parameters,
which in this case two, two values we got from the solver
and degrees of freedom
then it's just a difference between these two.
We're gonna use that degrees of freedom subsequently.
So I want to calculate the standard error,
that may equal to the square root
of sum of squares divided by degrees of freedom,
say we gonna use this in their calculations
for confidence limits.
So here I need to create a matrix that corresponds
to in the left column,
first column to derivative our respective Vmax,
so that just into being equal S value
divided by the quantity S plus Km.
So that's the derivative and I'll just copy here.
Next, it's a derivative of r respect to Km
if we do that again partial derivative,
it's gonna equal a minus sign Vmax
multiplied by S
divide by the quantity S plus Km and that quantity squared
and again, I want to copy values.
Now, I want to
name this as the X matrix.
So I'm gonna highlight go up here
and select table x for that matrix.
So the next step and notice not trying
to explain the reason for carrying out all of these steps
are this would be a very long screencast,
so instead of just trying to give it mechanics
how we go about getting a confidence limit.
So, this is taking the transpose the X matrix
and multiplying by the inverse of the X matrix
and to do that, we, since it's going
to end up being a two by two matrix, we entered a formula,
no way we are gonna enter the formula is matrix inverse
and then matrix multiply to transpose,
now these are all functions stored in Excel of X,
and then multiplying those two matrices,
and now the important thing is I enter not by hitting enter
but since it's matrix calculations, I do control shift
after, you can see I'd left out a parentheses.
So if you look up top here, you can see the inverse
of multiplying the transpose of X times X
and then taking the inverse
is the same as what I've indicated here.
Okay, so now we're almost finished.
So I've indicated five more parameters we have to calculate.
This is just the standard error
that were calculated earlier.
This value here squared,
this Svmax is equal to the square root
of one one element in that matrix
that we obtained by multiplication multiply by SC squared.
And similarly the Skm term
is equal to the square root
of Sc squared term
times the two two element in this matrix
we have tried obtained by multiplication
and then the offer term is 5%
because we're interested in 95% confidence limits.
So this is equal to inverse of value
from T distribution
and alpha, which I defined 5%
and degrees of freedom,
which I also labeled that cell eight,
and so we need to use this number also now
in our confidence interval calculations.
So finally calculate the confidence interval.
It's gonna be T value multiplied by Svmax
and likewise, the T value
multiply by Skm
and of course we can decrease the number
of significant figures here, I copied the values here
with fewer significant figures in that calculate regression
because more, not really justified.
And so now we've determined the confidence limits
and you can see, these are the values that we showed
at the beginning of the screencast.
So relative complicated procedure
you have to do quite a bit more
reading to understand all this the reasons.
what I've tried to show here is just what is the procedures,
so we can get the confidence interval for parameters
that we've determined from Nonlinear regression.