Welcome to Highline BI
348, class video number 48. Hey, if you want to
download this workbook, BI Chapter 04 and follow
along, click on the link below the video. Hey, we've done a
lot in this chapter. We've been doing linear
regression, scatter plots, creating estimated equations,
R squared, SST, SSC, all sorts of calculations. And we did it all longhand. And we did it with Excel
formulas and functions. But we got to look
at a great feature. Here's our x, that's
weekly ad expense. And here's our y, weekly sales. And we're hoping to predict
weekly sales based on weekly ad expense. We're going to do
all the calculations we did so far by simply taking
our data set, going up to Data. And if you have Data
Analysis feature added in, you can use Data Analysis. If you don't have it
added in, its easy. Go to File, Options,
down here in Add-ins, you click down here, Go. And make sure that
checkbox is checked. Escape. All right, so you're ready? I'm going to click
Data Analysis. And down here is the
Regression feature, click OK. Now we're going to make
sure we got our y values. And I definitely want a
field name at the top. Control Shift Down Arrow to
highlight out all the way down. Tab, the x input. Let me use Control Home to
jump up back up to the cell A1. Click in B1, Control
Shift Down Arrow. So we have y and x. Now we only have one x here. Next couple of videos, we'll
do multiple regression with multiple x's. And it's just as easy as
putting multiple columns in. And it will do simple
linear regression with a single
independent variable x or multiple regression. Now we definitely always what
field names, because the output is hard to read unless you
have field names at the top. We definitely want
to click this when we have field names at the tops. Confidence Interval, we'll
talk about that later. But I'm going to do all the
features that we should use, even though we're not going
to look at all of them now. So I'm going to put 99 here. By default, Confidence
Interval comes up as 95. So if you put 99 and then
it does one for 95 and 99. Now I want residuals--
we talked about that. That's the difference
between the actual y value and your predicted value--
and residual plots. We're not going to talk
about them in this video, but a couple videos
ahead we will. But that's how we want
our dialog box to set up, except for one thing. You've got to tell
it where to put this. I'm going to say, hey, go to E1. Now one thing about
this dialogue box, if you run different
regressions over and over in the same workbook,
it'll always remember whatever you did last. So you've got to make sure and
visually look through and make sure you've got all
right ranges and cells. And then click OK. Now I'm going to mess this
up, because that is not the way it comes
when you spit it out. It comes like that with all
the columns squished up. So you actually always
want to highlight the columns, from
whichever the start is, all the way to the end. And then double click
between any two columns. Double click on
that Best fits it. Now I'm going to drag the
residual plot down here. We'll talk about that awesome
automatic charting later. But let's just go through and
see the things we already know. I'm going to
highlight these three. And I"m actually going
to add some yellow. And I'm going to put
some notes off the side. Now multiple r, that really
is r, or correlation. R squared, we know
what r squared is. Standard error, we just talked
about that in our last video. That is standard
error of the estimate. Observation, that's
our sample size. Some of the things we
talked about in last video-- and I'm going to
expand the size. I'm going to change the
number formatting here. So Control 1. Come over to Number. Use a comma. And I'm only going
to show 0 decimals. And click OK. Now this SS means
sum of the squares. So when you see total
here and SS up here, that means that
intersecting cell is sum of the squares total. When you see Residual,
that's error or residuals. Those are synonyms. So when you see SS and
residual, you know that is SSE. When you see regression
and SS at the top, you know that's sum of the
squares of regression, or SSR. Here's our estimate of variance. That's where we
take the actual sum of the squares of
whichever one it is divided by its degrees of freedom. That's our estimate
for variance. So really all of these things
so far we have discussed. And then when we come
down here, we only have a single x variable. So we have two
coefficients, they call it, or estimates of
population parameters. So there's the intercept,
and there is the slope. Down here, we have
our predicted values. Control Shift Down Arrow. And right here we
have our residuals all automatically calculated. All right, so that's
pretty fast and easy. If you're doing a lot
of linear regression, then this is a great feature. The only drawback is,
of course, it is static. If you want to change your
data and have everything update automatically, that's
where our formulas that we learned earlier in
the class come in handy. Now we will come back
and talk about what f is and the p-value for
the f and the p-value for our individual estimates
of our population parameters and the residual plots
in upcoming videos. But so far in the
class, there's a lot of calculations that come
out automatically when you use the Data Analysis
Regression feature. Now if you come
down to DA Reg 3, I have like a cheat
sheet here that tells you all the individual
cells and what they are. All right, next
video, we'll come back and we'll talk about the
amazing Line Est function that will actually spit out a
bunch of these statistics all in one swoop. All right, we'll
see you next video.
