This video is an introduction to regression. This is part one of a two part series. In
this first video I will be going over a simple example using Excel regression. In the next
video I will be going over more complicated functions that can be used with Excel regression.
Regression is a tool similar to a trend line on graphs, but it's far more powerful. Trend
line gives you a few options for a line of best fit while using a regression analysis can
allow you to work with data that hasn't been graphed, and you can generate a more complicated
equation. It also gives you more information about on how well the regression model fits the
data. We will be using a specific example. If you drop an object from the roof of the
engineering center and measure its distance for the first 15 seconds of its fall. Use
regression to generate an equation to predict the distance fallen for your object after
a given time. In our Excel spreadsheet we have the data already listed. This data there
are two columns. The first is a column of distance in meters, and the second is a column
of time and seconds. This specific data I generate randomly for this. Now to start regression
we need to go to the data tab at the top of the ribbon. Data analysis is usually the last
thing on the ribbon, but as you can see it's not currently available. It is always automatically
installed with Excel but it's not instantly activated. To activate it we go to "File"
and down to "Options", and then "Add-Ins" over here. If we click "Go" next to "Manage Excel
Add-Ins" we could turn on the analysis tool pack. Now on the data tab there is the "Data
Analysis" button. This will give us access to a lot of tools available with Excel, and
what we're looking for currently is "Regression." Now this regression box is asking for a y
range and our x range. Since our data is labeled distance and time, d and t, rather than y
and x, we need to break down our problem a little further to figure out which part of
the data is the y range, and which is the x range. This problem is basically a distance
versus time problem. The x range is always the independent variable, while the y range
is the dependent variable. In the case of our problem time is the x variable, because
time is completely independent of anything else, which makes our distance our y variable,
since how far it's fallen is determined by how much time has passed. So if we go back
to our Excel spreadsheet, in our y-range we need to enter our distance, and I am going
to include the D at the top of that column as its label, and the x range we enter the
time, and since I did include the D and the T at the top I am going to select labels.
I also wanted to output the information into a new worksheet, and I wanted to output the
residual and the line fit plot. If I select OK, a new sheet is generated with my regression
information. As you can see some of this is difficult to read because the columns are
all at the same spacing, so I am going to space the columns out slightly to make it
easier to read. As you can see there is a lot of information here. There are several
things that are more important to note than others, especially in the beginning classes.
I am going to go ahead and highlight these to make them easier to follow. One of the
first things I've highlighted is the R-squared and the adjusted R-squared. Both of these
show the goodness of fit for your model according to the actual data. The difference between
them is that the R-squared is a statistical measure of how well the regression line approximates
the actual data points. If it's R-squared of 1 that means it's a perfect match. The
adjusted R-squared however is slightly different. It's usually lower than or equal to the actual
R-squared, because it will penalize you if you use too many x values to approximate the
data. For example if you have 100 data points, and you end up using x to the 100 you are
going to get a very good approximation because it will match the data perfectly. However,
it's not going to be a very good fit for the actual data, it's just forcing it to fit.
So both of these are very important. The closer they are to 1 or -1 the better they fit the
data. In this case the R-squared and adjusted R-squared are both around 0.92, which is a
very good fit. Now down here you have the coefficients. If you remember, when we selected
the data on our velocity tab we included the D and the T. That's because over here the
T, which is our x value, is listed as the x value with this coefficient. It doesn't
seem like it's too important now, but if you end up having multiple x values you need to
be able to keep them straight, and including those labels helps with that. So the coefficient
for T is 73.3, and the coefficient for intercept is -171, and the intercept is where it crosses
the x-axis. Now if you remember back with our data they're both zero to begin with,
so something doesn't seem quite right with that number, and then over here is the p-value.
The p-value is telling you how important this piece of data is in your equation. So for
both the intercept and our T the coefficients are extremely important in this particular
equation, because the p-value is less than 0.05, and for the T value it doesn't look
like it immediately, 2.72, until you realize that this is in scientific notation. So when
you think of this having the intercept at -171 doesn't seem to make sense for the data,
yet you still get an R-squared and adjusted R-squared of 0.92. This is why we included
the plots. Now if we scroll over this way this is our line fit plot. D is distance,
so the blue dots is the actual data that we have. The red dots is what's predicted using
this model, and yes they are very close, but you can see that there is a trend in our blue
dots that's not being displayed in our red dots, and you can not always see this on your
line fit plot however, which is why we also included the residual plot. If there is an
obvious trend on your residual plot, that means there is a trend in your data that is
not being accounted for with this particular model. So there is a, this nice curve here
in our residual plot, which is saying that there is something happening with our original
data that's not being shown using this equation, but we're still going to write this equation
down. So if we go back to our example problem and go down here, the first equation we came
up with is D equals 73.3T-171.71, because the 73.3 is the coefficient for T, and the
negative 171 is the intercept, and this had an R-squared of 0.92, but I am going to list
that it was not a good fit. So if we go back to our Excel spreadsheet, we can try and do
this again. If there is a curve in our data that's not being taken into account with a
single linear regression, then we can try to do a slightly different regression. We
could include a T^2. To do this, I am just going to square everything in the previous
column. Now if I go back to "Data Analysis" and "Regression," my y range remains the same,
but my x range, instead of just being this column, I am going to make it both. I am still
including the labels, I still want a new worksheet, and I still want both plots. So if I click
OK, I have another sheet generated. I am going to go ahead and reformat this sheet to make
it easier to follow. As you can see the R-squared and adjusted R-squared have both gone up to
0.998, the coefficients for the intercept is now 3.72, much better than the -171 it
was before. The T coefficient is -1.87, and the T^2 is positive 5.01, but if you look
at the p-values for both the intercept and the T your p-value is greater than 0.05, which
means that neither of these are significant in this equation, and if we go to look at
our plots, since we did a T and a T^2 it generated two sets of plots, but in both set you can
see that the predicted distance and the actual distance are very close to each other, and
if you look at the residual plots there is no obvious trend in either one. So there is
a possibility that this is a very good fit for our data. So we're going to go ahead and
write this down in our problem set, and I am going to write down both versions of it.
The first version is the full equation, and the second equation is excluding what Excel
decided were not important for the equation, and this was with an R-squared of 0.998.
Now there is one more thing we can test. Since Excel did not consider either of these important
for the equation, we can run the regression again, excluding both of them, so if we go
back here and go to "Data Analysis," "Regression," we keep the y range the same, the x range
we're just going to have for T^2, and I am going to tell it that the constant is zero,
so we're going to force regression to put the line through the origin of the plot. Now
if we look at this the R-squared value has gone up to 0.999. However, the adjusted R-squared
has gone down to 0.93. It means that the change we made, although it has made the fit closer
to the actual data, it's possible that it has decreased our actual fit by making it
too accurate, or too close to it. In this case I think I am going to go with the R-squared,
since all we did was remove the two values it considered unimportant. Here our intercept
is zero, and the coefficient for T^2 is 4.88, and our p-value is very close to zero, it's
1.1X10^-24, and if we look at our plots, there is no obvious trend in our residuals, and
our line fit plot is already extremely close. So I am going to include this on our problem
set. It does have the highest R-squared value, and for comparison the original data was not
from an experiment, I had the times and I generated a distance using D equals one-half
gravity times T squared, and then after that I adjusted each value up or down slightly
to try and simulate experimental data. One-half gravity happens to equal 4.91 meters per second
squared, and which we got very close too in our third equation.