How to Run a Multiple Regression in Excel 2007

I take some pulse rate data and relate it to incline and walking speed on a treadmill via a multiple regression analysis. In addition to showing you how to get the multiple regression output, I interpret the results.

---

Closed Caption:

hello this is a second video in a series
on multiple regression in this video
I'll show you how to run the multiple
regression using Excel 2007 we're using
the same data is in the first video
pulse rate data that I collected a few
years ago walking on a treadmill we're
using inclined as x1 speed as x2 they're
both quantitative variables and pulse is
my quantitative response and then the
previous video I showed you how to make
a scatter plot matrix it's still here
i'll get rid of this however i'll just
delete these scatterplots these merge
cells to ok and what I should have also
showed you in the last video is how to
make a correlation matrix so i can do
that quickly let's go data data analysis
correlation press ok here's my input
range again we're ignoring the time .
but it may be an important factor labels
in the first row output range let's put
that right there
press ok here's my correlation matrix I
can edit this a little bit
we don't need these ones down the
diagonal I can totally get rid of this
column so I raise some blank i'll just
delete the whole column and i'll delete
this role
ok so and let's format to three decimal
places or correlation okay and these
correspond to the picture somewhat that
we saw on the scatterplot matrix their
relationship between pulse and incline
can be summarized with Pearson's
correlation coefficient there's a
moderate positive relationship and for
speed a little bit stronger and then
between the 2 X variables inclined and
speed no relationship and I again I had
done that on purpose because I
was able to choose the x + 1 and X 2
values inclined and speed so that they
were unrelated to each other so i did a
designed experiment here choosing my X
values and randomly assigning the
carbonate the treatment combinations to
my subjects hear that . that time
periods
ok let's get rid of this and what we see
from this correlation matrix and the
previous scatterplot matrix is that it
looks like inclined and speed have some
kind of relationship to the response so
maybe together they'll help us
understand the response ever better than
an individual simple regression would do
using one variable at a time
ok let's go data data analysis
regression press ok my responses pulse
and my input X range are both inclined
and speed press ok our highlight labels
in the first row output range
let's put that right here and we're not
going to do anything with residuals at
this point press ok here's my regression
output again I like to clean it up a
little bit so i'll stretch out this
first column to about a with 12 we have
duplicate confidence interval
information here so i'll delete the
duplicate part just erase over it i like
to format to three decimal places
generally ok and then we could format
maybe this to the numbers are kind of
large in the ANOVA table here I'll
format the one decimal place with a
comma
this is really the p-value for the
f-test I'll just abbreviated
significance of F and these are
coefficients these are my standard
errors and this is my adjusted r-squared
and this is my guess
residual standard deviation with the
excel called standard error
ok so this looks like nice clean up
output i like to write the regression
equation right below it so our response
here is pulse so these are are fitted
pulse is makes up our equation and I'll
know the intercept plus the slope
coefficient for inclined plus the slope
cool coefficient for speed
there's my multiple regression equation
now in another video i'll show you that
since i have a quantitative x1
quantitative x2 and a quantitative
response this equation can be graphed as
a plane in three dimensions and I'll
show you that in another video but for
the rest of this video let's just
interpret the output quickly our main
statistics and regression output r r
squared s the residual standard
deviation r squared is the coefficient
of determination in this case 79.1
percent of the variability and my pulse
rate is explained by inclined and speed
my standard error s or residual standard
deviations tells us that the typical
deviation between the actual pulse rate
and what this model says they should be
is about 4.67 pulse units that doesn't
seem too bad
given that my pulse rates were in the
vicinity of 1 10 to 150 years the ANOVA
table
overall the regression is significant
have a very large f-statistic and then
we have our coefficients down here x
intercept this says that my pulse rate
would be 58 if I wasn't walking and my
shadows and I had a0 inclined but that
would be an extrapolation because i did
not have any data here where I wasn't
walking even though I did have an
incline of 0 so we wouldn't interpret
this as a . prediction given inclined
and speed are 0 however that's the
height of my plane and I
it to make predictions within the range
of incline from 0 to 8 and pulse from
3.6 24.8 my coefficient for implying is
telling me first of all I know that it's
statistically significant and so is
speed my coefficient for incline is
telling me of holding speed constant I
can expect my pulse rate to go up on
average by one point seven units for
each one unit increase in the incline
scale and this confidence interval
output over here tells me although this
is my best guess as to how incline is
affecting my pulse rate this gives me a
range of reasonable values for how that
impact really is so in other words
there's a ninety-five percent there's
ninety-five percent confidence that the
actual impact is between 1.2 units and
2.3 units on my pulse rate scale for
each one unit including one unit
increase in the incline ok a speed this
says for each one mile per hour faster
i'm walking on the treadmill holding
inclined constant I can expect my pulse
rate to be higher by about 15 units and
that is statistically significant and in
fact it's t ratio is a little bit bigger
than the incline t ratio so this is the
slightly stronger variable
ok so that's interpreting the results of
a basic multiple regression model only
two predictor variables
that's it