Welcome to Highline BI348 class video number 52.
If you want to download this workbook BI348 chapter four
and follow along, click on the link below the video.
And this is our last video for chapter four.
We want to talk about multiple regression hypothesis testing.
Now here is a data set we used in our earlier video
about multiple regression.
We want to be able to predict risk of stroke
over the next 10 years, given age, blood pressure and smoker.
So here's our raw data.
We need to calculate our sample statistics
for slope and y-intercept.
And do hypothesis testing to see if there's
a significant relationship.
Now, last video we did- and if you scroll over here
you could see, hey this is awesome.
This is data analysis regression feature.
A few clicks and we have everything we want.
But that doesn't work if your data is changing.
So the beauty of Excel formulas, if we have this linked here
and any of this changed, then instantly everything
would update.
Now we can get most of the regression statistics
that we need from the line s function but not all of them
so we use the LINEST Function up here.
And then down here, we use a couple extra functions.
Now here's the deal, 1,2,3 independent variables.
That means when you're using the LINEST or line
straight function, you have to highlight in advance 1,2,3,4
columns.
It's always one more column then there
are independent variables.
And here's the reason why.
It will list 1,2,3 slopes and the intercept like that.
Now there's a strange thing about using LINEST
to deliver multiple statistics.
Notice we have x1, x2, and x3 from left to right,
but when it lists the slopes it's
going to lift list three first then two
then one left to right.
So you have to be aware of that.
And I usually have a little template like this reminds me
what all the statistics are.
All right so 1,2,3,4 columns.
One more than their independent variables.
And always five rows.
And then we type equals LINEST and it's a line straight
or line estimate.
It wants the known y's.
There we go.
Comma, and the known x's.
And notice we're not including the field
names like we did in our regression data analysis
feature.
And then comma.
I'm going to calculate B normally, so I'm putting a 1
for true and comma.
I want all the additional statistics, past slope
and intercept.
So I take one close parentheses.
LINEST is an array function.
It's going to deliver an array of values
simultaneously into a bunch of highlighted cells.
So we have to tell Excel that we're
doing an array function with the key stroke Control-Shift
and Enter.
Now if you look up here you can see those curly brackets.
That means Excel understood that we entered this
as an array function.
Wow.
Look at that.
We have slope for R3, which is the smoker, slope for two which
is our blood pressure, slope for three which is our age.
And finally y-intercept.
And right there that's the standard error for slope,
x sub 3.
Standard error for slope, x sub 2.
There's the standard error of the standard deviation
for that slope and for the y-intercept.
Here's the coefficient of determination.
Right here is going to be our standard error of y,
degrees of freedom, SS, residual or error, SSR and there's
our F statistic.
Now we do want to calculate our predicted and residuals.
And I already went ahead and did this.
I multiplied each one of the slopes
times each one of x's and added the y-intercept,
and copied it down.
And then calculated our residuals.
Now we need to plot the residuals against our- each one
of our x's.
And I'm going to do this one time.
I'm going to highlight the residuals
and then hold Control, highlight H and x's on the left.
So this should work.
Insert.
We go over to Scatter, Markers, green arrow,
axis titles, solid line around this one.
Equal sign shoots me up here.
I say Residuals and Enter.
I click on the vertical axis, equals age and Enter.
So there's residual plot one.
Now watch I'm going to totally cheat.
I'm going to copy this chart here.
Control-C. Click down here Control-V.
Click on the actual values.
I'm simply going to move this right here to blood pressure.
Click on this axis equals sign to shoot me up here.
And then click on blood pressure.
Instantly add residuals for blood pressures.
Then I'm going to do the same thing, Control-V.
Click on the markers.
Click on the edge here and drag it over.
Click on the axis equals sign.
Click on the smoker label here and Enter.
So I have my residual plots.
And so we're looking and those don't
show any strong violations so we'll
continue with our process.
Now once we know that we can do inference,
there is our F-test statistic but there's no P value.
So we have to use the equals F dot
and there's a DIST dot right because there's
going to be a right tailed test, the distribution functions
always deliver a probability.
So there's our x that's going to be our f, comma
and degrees of freedom.
The first degrees of freedom is the number
of independent variables.
I don't have that anywhere so I'm
going to count the actual slopes here.
1,2,3.
So the information will come right from there.
That's the degrees of freedom 1, comma degrees of freedom.
We have 20 spit out from our line estimate function.
Close parentheses and there is the P value.
Incredibly small.
Now the T step for each one of our statistics- well the T set
is simply the actual statistics divided by the standard error.
So I am going to say equals relative cell
reference that many up.
And then relative cell references.
Get the standard deviation.
Control-Enter.
And copy this over.
And so for each one of these, we have our t-Test statistic.
And we need p value for those.
We're going to use equals T dot- and this is a two tailed test,
so I'm going to use two tailed.
Now this is a new relatively new function.
And that x requires that it's on the upper end.
So in case you have a test statistics that's negative,
we got to use the ABS, the absolute value function.
All right I'm going to click on the test statistic.
Close parentheses.
That's our x, comma and degrees of freedom.
There it is.
And I'm going to lock it with the F4 key.
Close parentheses.
Control-Enter and copy it over.
So in all cases all of these are much smaller
than our alpha of 1 or 5%.
So in all cases we would reject the null hypothesis
and accept the alternative.
It's reasonable to assume- it's very reasonable to assume
that the slopes and the y-intercept are not zero.
All right, so in our last video for regression chapter,
we saw how to use the LINEST function.
And it's a little bit harder than doing our data analysis
regression feature.
But if you need formulas to link to the data and update,
then that is your ticket.
And we also saw how to calculate the t-test statistic,
how to use T dot, DIS dot, two-tail and the ABS function
for our P value for our T statistic.
And F dot, DIS dot right, for our P value for F. All right,
this is our last video for chapter four.
We'll see you next chapter and next video.