Welcome to Highline BI 348 class video number 50.
Hey, if you want to download this Excel
file, BI348-Chapter04, or the PowerPoint file,
click on the link below the video.
In this video, we want to have an introduction
to multiple regression.
Now, so far in this chapter, we've
done a lot with simple linear regression.
We calculated the slope, the intercept, r squared,
and other measures.
Hey, let's go over briefly to our PowerPoints.
We're going to have an estimated regression
equation, whereas earlier we had simple for one
independent variable.
Now we're going to have multiple regression.
And here's our equation.
There's y hat.
We're going to have slope 1 times x1 plus slope 2 times x2
plus all the way to our last slope
and x value plus some y-intercept.
Now, we're going to jump back over to Excel.
Now two videos ago, we saw how to use
data, data analysis regression feature,
to calculate all sorts of statistics for us,
including our slope and intercept.
So that's what we're going to do here.
Now here's our data set.
Annual credit card charges, that's our y value.
That's going to be the value we're going to try and predict
with our equation.
And our x1 is going to be annual income.
x2 will be number of years post-high school education.
So we'd like to build an equation that can predict
based on these two inputs.
So if an application comes in and says $50,000
with two years of post-high school education,
we want to be able to estimate what their annual credit
charges will be.
All right, so we're going to use the data analysis feature.
Data, Data Analysis, and over here, there's Regression,
an I click OK.
Now, y input-- I'm going to click
in the top cell, Control-Shift-Down Arrow.
Control-Backspace to jump back up.
There's our full range tab.
And now there's our x values.
And differently than earlier in the class where we only
had one, we're totally allowed to put two x variables in.
Highlight the labels, Control-Shift-Down Arrow,
Control-Backspace.
So there's our x ranges for the B and C column.
Now we definitely want labels at the top
because our output is hard to read
unless we label everything.
So I'm going to check this.
That says yes, we have labels in our data set.
Confidence interval, I'm going to change this from 95 to 99.
We're not going to talk about this total hypothesis testing
next video.
But we put a 99 here because if we don't, the output
will deliver two confidence intervals, both at 95.
We definitely want our residuals.
And we definitely want residual plots,
which we'll use for the first time next video.
But that's how our dialogue box is
going to look when we run multiple regression.
Now the output range, I'm going to click right in F1.
Make sure I got my ranges, click OK.
Now, it says some data will be hidden because I
have some stuff over here.
One of the charts will appear on top of it.
That's OK.
So I'm going to click OK.
Now with our output from the regression feature,
we usually have to highlight all of the columns,
and double click between any two columns,
and best fit all of the columns.
Now, I'm going to drag the first residual plot down here,
and the second one down here.
And we will talk about these next video.
It's amazing that it just spits those out.
We don't have to manually calculate those.
And I'm going to drag this over.
So I'm going to highlight and point to the edge.
When I see my move cursor, I'm going to click and drag.
Now, here's our output.
And we talked about most of this in the two videos ago.
There's our r correlation.
There's our r squared.
Now that is 53%.
So it's not as strong as some of our other examples we had.
But it means that 53%, or about 54%, of the variation in y
can be explained by our model or our equation.
There's our standard error, our number of observations.
And down here, here's our intercept.
There's slope number one and slope number two.
So up here, here's our equation.
It says y hat and annual income.
So that means since we're trying to predict annual credit card
charges, 14 and 1/2 pennies of annual credit card charge will
increase for every $1.00 of annual income.
Now when you're talking about a slope and an x,
you actually hold the other variable fixed
while you change this one.
So for every $1.00 of annual income,
while we hold the other variable fixed that we increase,
total credit card charges for the year will increase by about
14 and 1/2 pennies.
Similarly, over here, when we hold
this one fixed, for every one year of post-high school
education that we increase, the annual charges
will be predicted to go up by about $771.
Now just as we did earlier, when we
go to plug in a value to make a prediction,
we have to make sure that we look at the min and max
for that particular variable.
So for our prediction with our equation,
when we put a number in for annual income,
it's got to be between the min and max, so about $10,000
to $135,000.
Similarly, for x2, that's number of post-high school years
education.
The min was 1 and the max was 0.
So when you plug something in here,
we've got to make sure that be between those two values.
Now we can make our equation equals-- and I'm
going to take slope 1 times-- and there's
our x value-- plus slope number two,
that's the 771, times the x2 input for that
plus our y-intercept.
When I hit Enter, our prediction for this particular applicant
who said they had $54,000 annual income, two
years post-high school education,
the estimate will be $7,529.
The next applicant says they had $190,000 and 0 years.
Neither one of those are allowed for our model.
They don't fit into our experimental range.
Control-Z-Z. So we want to make sure to put inputs
in that match the evidence that we have collected over here.
So $69,500 with 4 years of post-high school education.
Annual charges estimated or predicted to be about $11,000.
Now, we want to go look at a second example.
And we want to notice something about all
of the x's and y's that we've done throughout all
the videos in this chapter.
They're all quantitative or numbers.
We need to see what happens if you
have a categorical variable.
So we're going to go over to the sheet Categorical.
Now here's our data set.
We actually would like to predict
percentage of risk of a stroke over the next 10 years.
That'll be our y value we're trying to predict.
And we've collected data on age, blood pressure,
whether or not they're a smoker, yes or no,
and then the actual current risk.
Now, the trick is when we have categorical data like this,
is to convert it to a number.
So we're going to convert yes to 1 and no to 0.
So we need to add an extra column.
And when we run it, we'll have three x's and one y.
This will not be included.
But it's going to serve as our lookup.
So I'm going to use vLOOKUP.
We're going to look up the categorical variable for smoker
either yes or no, comma.
Table is going to be this table right here, and F4 to lock it.
Comma, the second column has the thing
I want to go get and bring back.
And it's called a dummy variable.
So I'm going to type a 2.
Comma, this is-- it is sorted, but I'm going
to keep it as exact match.
Because we're looking up a word.
And I'm not going to put false.
I'm going to put a 0.
Either one is fine-- 0 or false.
Control-Enter, double click, and send it down.
Now, this is going to be x3.
And it will do something interesting to our equation
once we get our slopes.
Because if it's no, then 0 times whatever the slope is for the x
will be 0.
It's only when we get our yes, and it will be 1 times
whatever our slope is.
All right, so let's go ahead, click somewhere in our data
set.
We have Data, Data Analysis, there's our regression.
Click OK.
Now notice, it's trying to be polite and remember
from our last example.
But it is not correct.
So I'm going to make sure and highlight the y.
Tab-- and there it's not correct at all.
I'm going to highlight three labels and then
all of the records.
Labels, confidence interval, output range is not correct.
I'm going to highlight this and click, say, J1.
Residual and Residual Plots, when I click OK,
I have some stuff off to the side.
It'll be OK.
The charts will be on top of it.
When I click OK, notice, I already
have the correct column widths here.
So I'm going to click and drag these.
Again, we'll use these when we check our assumptions
for our least square method and then do
hypothesis testing next video.
Now, let's look at our output.
r squared says that about 82% of the variability in y
can be explained by our regression equation.
If we look down here, we have intercept and one,
two, three different slopes.
Those, we mentioned that if you don't have field names,
it's really hard to interpret.
This, it's so nice that we have our labels right here.
But there it is, the slope for age, blood pressure,
and smoker.
Now as I said, something interesting
happens with a categorical variable.
Remember that x sub 3 can either be 0 or 1.
Now, I want to go over to our PowerPoints
where I've typed out the equation.
Here's our output down here.
And here's the full equation-- y hat,
and holding the other two variables constant as we
increase age by one year, the stroke risk factor
goes up by 0.835.
Holding the other variables constant, as blood pressure--
and I can see I misspelled that-- as blood pressure goes
up by 1 point, the risk goes up by 0.228.
And here's our smoker variable.
Now think about this.
This can be 0 or 1.
And that means that we'll in essence
have two different equations.
Now it's fine.
This equation here will work.
You just put in the 0 or 1 and calculate it, right?
But look what happens if we're a smoker.
Here it is down here.
So the same equation, but if that's 1, it's 1 times 10.61.
So 10.61-- and again, that's the slope that
means that's how many points it goes up if you're a smoker.
But that is a constant if you're a smoker.
So you'd simply subtract the y-intercept.
And this would actually be the equation if you're a smoker.
So the first two are the same.
But because this is 1, it's a constant,
we add it to the slope.
So the slope would go from minus 72.51 down to minus 61.9.
Now here's the y hat if you're a non-smoker.
We just leave that x3 variable off because we know it's a 0.
All right, so in this video, we saw
how to use data, data analysis regression feature,
to generate our statistics for multiple regression.
We saw a data set where we wanted
to predict percentage risk of stroke
given age, blood pressure, and smoker, a categorical variable.
And then back on MR1, we saw a data set up
where we wanted to predict annual credit card
charges with two x-- annual income and years
post-high school education.
All right, next video, we'll see how
to test our assumptions for the least square method
and run hypothesis testing to see if it's reasonable
or not to use these as our sample statistic set,
our regression equations.
All right, we'll see you next video.