INSTRUCTOR: In this screencast,
I'm gonna talk about using Excel Solver
to determine the kinetic parameters
for the enzyme kinetics
that obey Michaelis-Menten kinetics.
You're gonna use nonlinear regression.
And other screencasts explain why you should be using
nonlinear regression to determine these parameters
and the data I'm gonna use are from reference given here
and in the other reference
explains how to use
Excel Solver for this type of calculation.
So we're listing the concentration of substrate,
or I should say the number of moles
since I'm taking the data that's in the table,
they presented this way.
Obviously, concentration should be divided by volume
but that doesn't change anything about the calculations
and then the rate number of moles or micromoles
that react per minute.
So we measure the rate at various substrate values
and then, try and determine Vmax, the maximum rate
and Km is the Michaelis constant.
So what I'm gonna show you first are the results
and then we'll go to a spreadsheet
and look at how we actually do this.
Here are the values for the two constants.
And then their 95% confidence limits.
So, in this screencast, I'm just gonna show
how to determine these two values.
In another screencast I'll show
how to determine these confidence limits.
And we would look at the r squared value
and say it looks like the fit is pretty good.
That's not a great indicator
but that's certainly something that
if it was not close to one,
we would say it's not a very good fit.
And then, we can look at the plot of the rate
then we can compare the model to the data
and so let me show you that plot.
So on the left is the rate plotted
versus the micromoles of substrate.
The green squares are the data
and then the orange connecting the points
for the calculated values show the fit,
looks like it's pretty good.
The residuals are shown in the right plot,
residuals being the difference between the measured rate,
experimental rate and the calculated rate.
And the residuals don't seem to show any pattern
indicating again, we have a reasonable fit.
The confidence limits also indicate
that the fit is pretty good.
So now let's look at the spreadsheet,
how we actually do this calculation.
So here's the spreadsheet and what I've done is enter
the data that I just showed you.
I've written the equation I'm gonna be using
to fit the data.
And then, the two parameters that we wanna determine,
this one is cells we'll be changing, Vmax
and I've named up here, Vmax
and I've named this cell Km to make it easier
in the calculations.
Now, what I'm gonna to do is put some labels for columns
and I'll explain what they are after I pause
to input that information.
So this column, r calculated,
is the value calculated
from Michaelis-Menten kinetics
for their parameters and we're gonna guess
their parameters initially.
And let me guess, this as one
and guess this as 200.
And of course, having some prior knowledge
is important because we want the values
to be not so far off
that perhaps we won't converge with Excel.
And so then, what I'm gonna add to here is
a formula to calculate the rate
using these parameters.
So that means Vmax,
since I've named that cell,
multiplied by the substrate
and divided by Km.
I've named that other cell and added to it
is the substrate.
And so, this calculates a rate for these parameters,
it's not the same as experimental rate
because I arbitrarily guessed these values,
but what we wanna do now,
copy of this into your other cells.
What we wanna do is calculate
the difference between these two,
square it and then minimize the sum of those squares.
That's the approach for nonlinear regression.
And so here, all I'm gonna do is enter experimental value
minus calculate value, quantity squared.
And then of course, copy this in all the other cells.
And so, let me enter a couple other things
and we'll see later why they're important.
Let's look at the difference between
the measurement and calculate value.
So that's just experimental value
minus the calculated value,
that's known as a residual.
And the residuals are important for determining
how good our fit is.
And then the other calculation here
that we'll see reason for this,
it's gonna help us calculate the r squared value.
It's the experimental value minus the mean of these values.
So I'm gonna calculate here the mean,
as you can see I've entered the average
of the measured rates
and so this value is the average.
I'm going to sum up these values
so you can see the equation
for the sum here and this is where we're gonna minimize.
So what I'm going to do is just to reinforce
that's the cell that we're minimizing.
Well I can also calculate the experimental value
from the mean.
And to make life easy I'm gonna name that cell rmean.
And that way, I then calculate here experimental value
minus r mean quantity squared
and I can then copy that into these cells.
So I'm about ready to do now the regression.
So to carry out the regression, go to the Data,
to Solver and I'm gonna pick the cell here,
I want to minimize it.
And then, I'm going to change these two cells,
so I select them, hit Enter.
And constraining so the variables are non-negative
as we expect they should be
and I hit Solve,
it gives me an answer.
This is the values I showed you earlier in the screencast.
And so, we've minimized the sum to the squares.
You can see here is the value here,
.00071.
And then, the difference between the experimental
and the calculate values are over here
and calculate what we call a run number to get an idea
if it's a random distribution.
Run number being how many sequences of the same sign
either above or below the zero value.
So this is one, that's two, that's negative
and then positive one is three, negative four,
positive five.
What we expect for 10 data points
is half that number plus one, it's six,
so we're pretty close
that it looks again like a fairly random
distribution of residuals.
So on a subsequent screencast,
I'll explain now how we get the confidence limits
for these values.