In this screencast I'm going to introduce you to mathematical models,
particularly making mathematical models from experimental data. This is also
known as a regression equation or a regression model. We're first going to
look at simple linear regression. That is just an equation for a line. Some of you
may have seen the format y = mx + b and that's the exact same format.
What I'm doing though is using a slightly different notation so in this
example the y = mx + b, m would be equivalent to beta_1 and B which is
the intercept would be this coefficient beta_0 so beta_0 and beta_1
and other betas are just coefficients. They're constants. So I'm going to go
through an example. I've got actual data for. What we've got is as a function
of the year from 2000 to 2009, the average yogurt consumption in the United
States pounds per person per year. If I just wanted to look at this in a plot I
could select both of those and I can go up here to the insert tab and charts and
I can just make a quick scatter plot. Now obviously that looks a lot like a line.
I'm going to go ahead and format the axes. You can go in here and you can
right click on a data point and you can go down here to add trendline and over
here on the right you can display the equation on the chart. You can also
display something known as the correlation coefficient which is R but
this is then the equation that relates y which is our yogurt consumption with x
which is our year. So then if I wanted to, you know, easily predict in the future
perhaps then you could just put that year into this equation and you could
get an estimate for the yogurt consumption. I'm going to show you a
different way to create this equation of the line and there's a couple reasons
for that. The first one is using the regression tool in excel you can get a
lot more information out of it than just what's shown here. In this example here
we know that the slope is approximately 0.6825,
however there's uncertainty with that and when you take more advanced
statistics courses you'll learn how to estimate the uncertainty but I just
wanted to show you how we can use the regression tool to get us something
known as the confidence intervals about the slope and the intercept
which is -1359. I'm gonna go ahead and I'll leave
this up for now but I'm going to show you how to use the regression tool. I'm
going to go up here to the data tab. Now if you don't have the data analysis
thing over here the data analysis tool you can go into file options click on
Add-ins and then at the bottom click Go and then make sure that the
Analysis Toolpak; it doesn't matter if you're using the VBA one or not, just make sure
one of those is selected and go ahead and click OK and now when you do this in
your data tab you should have the data analysis tool over there. So to do
regression we're gonna click on data analysis. It's gonna bring up this box
we're gonna go down here to the bottom and click regression and then click OK.
It brings up the regression tool. Yogurt consumption depends upon year so we're
gonna put for the input y I'm gonna highlight starting the first row. You
notice that first row contains labels. So because of that I'm gonna click
labels here the input x I'm gonna select A1 through A11 and I'm just gonna go
ahead and leave everything else I'm gonna click OK. And what it does is it
creates this new sheet and if you click somewhere else and then go up here and
drag and select all of those columns then you can click on one of the
borders between the columns just double click and it just auto formats and auto
rescales everything. The most important thing for
us is the intercept of -1358. You notice that that's exactly what we
got back here in the plot. We have -1358 as the intercept and the slope of
about 0.6825. So it tells you the same values however the important things are
these confidence intervals and p-values which you might learn about later
but this is telling us that we're 95% sure that the intercept lies between
those two regions. Remember we can never be a hundred percent sure of the
intercept and we're 95% sure that the slope lies between these two values.
So this is a good way to create models, regression models, another thing it tells
us is if we go back here to the plot this is known as r-squared but you'll
see in more advanced statistics courses that really a better measure of a good
model is not the r-squared but it's something known as the adjusted R
squared which you can only get from the regression tool. So this screencast shows
you how we can plot to get an equation that's typically not done. Engineers
typically just create the regression model so that you have statistics about
your model. Thanks for watching.