This screencast demonstrates how to use excel for multiple regression. Here we have initial
reaction rate data and what we need to do is fit that data to this equation. By doing
that we need to find our parameters "k", "a" and "b". In order to do this in excel the
first thing we have to do is linearize the equation. The way to do that is by taking
the natural log of both sides. This is going to equal the ln(r) = ln(k) + a * ln[O_3] +
b * ln[B]. One thing to be careful of is our reaction rate data. If you notice what it
says here is r * 10^7. What that really means is that the first piece of data is not 1.5
but rather 1.5*10^-7. The meaning of that is if you take this 1.5*10^-7 and you multiple
it by 10^7 you will get 1.5. Now we have to set up our spreadsheet so that we can use
this equation to find our "k", "a" and "b". The first thing is not to forget to put the
reaction rate as it actually is. That equals this 1.5*10^-7. The next thing that we have
to do if you notice this is the natural log of O_3 and this is the natural log of B so
we have to put columns for that. In addition our dependent variable is the ln(r) so we
have to find the natural log of "r" as well. This first column is going to be the natural
log of "r". This second column is going to be the natural log of "O_3". This third column
is going to be the natural log of butane. Let's go to data regression. On our tool bar
we click data and over here we have data analysis. If you look at all of the choices for data
analysis you see this regression. You hit ok. In this particular input box the first
thing we need to do is input the "y" range which is our natural log of "r" however what
is different in multiple regression is that you can have more than one "x" variable. Here
we have both of these columns as our "x" range. We click ok. The first thing that we notice
is our "R" squared is about 0.93 which is ok but our adjusted "R" squared is 0.89 which
is lower than we wanted but part of it comes from that we only have 6 data points. The
important information is here. That intercept is the ln(k) which is -11.2622. The "x" variable
one is our "a". Finally this "x" variable two is our "b". Let's go back to our problem
and put those in. Remember we are trying to find our "k", "a" and "b" so we can fit this
model. Let's write down what we found from our regression. This is the ln(k). We need
"k" so in order to find that "k" is going to equal exp(ln(k)). When we calculate that
out we find that our "k" is 1.3*10^-5. Finally we can write our reaction rate such that r
= 1.3 *10 ^(-5) * [O_3] ^ (-0.031) * [B] ^ (0.161).