\magnification=\magstep1
\centerline{MONT 102N -- Environmental Mathematics}
\centerline{Lab on Linear Regression, Correlation, and Data Analysis}
\centerline{October 19, 2011}
\bigskip
\noindent
{\it Background and Goals}
\bigskip
In this lab, we want to use some of the ideas we have been
discussing about regression, correlation, and so forth
on the problem set for this week and try to understand
the patterns that are there.  We will use many of the statistical
features of the Microsoft Excel spreadsheet program.
\bigskip
\noindent
{\it Regression and Correlation in Excel}
\bigskip
\noindent
To compute the least squares regression line and correlation
coefficient for an $x$-$y$ data set, as we saw in class on 
Monday, the following options are available in Excel:
\medskip
\item{$\bullet$}  Normally, you would start by entering the 
$x$-values in one column and the $y$-values in another parallel column.
So that things line up properly, put the $x$- and $y$- values
from the same data point on the same row.  Put  
a text header in the cell at the top of each column to help 
identify the two quantities.  
\item{$\bullet$}  To generate the $y$-versus-$x$ scatter plot, 
highlight the cells in those columns, then press the Insert tab on the top, look
for the Scatter option under the Charts category and press that
button.  You can experiment with the different plotting styles.
Each time you generate a plot, it is overlaid on the grid, but
you can drag and drop the plots to rearrange them if you want.
\item{$\bullet$}  The coefficients $m$ and $b$ in the equation
of the least-squares regression line can be computed and displayed
in cells of the spreadsheet like this.  For example say you 
have entered the $x$-values in cells $A3$ through $A25$
and the $y$-values in cells $B3$ through $B25$ (with column headings in
$A2$ and $B2$). 
\itemitem{1.}  First go to an unoccupied pair of cells on the same row, and
highlight them
\itemitem{2.}  Enter {\tt =LINEST(B3:B25,A3:A25,TRUE,FALSE)} and
press SHIFT/CTRL/ENTER together.  Note:  the $y$-values {\it must} come
first, then the $x$-values, or else you will be computing a
regression line of the form $x = My + B$.  That actually does
makes sense, but it is not what we want here(!)
\itemitem{3.}  The slope value $m$ will be computed and
put into the left cell where you entered the LINEST command.
The intercept value $b$ will be put into the cell next to that
one to the right.   
\item{$\bullet$}  The Excel command for computing the correlation
coefficient $r$ is {\tt CORREL}.  With data as above, in any convenient cell,
enter {\tt =CORREL(A3:A25,B3:B25)}. 
\item{$\bullet$}  There is also a {\it second, alternative} 
way to generate the numbers we want from a linear regression, 
together with a lot of additional statistical information 
including some useful plots. 
From the Data tab, select Data Analysis, highlight
Regression in the small Data Analysis window that comes up,
and press OK.  Fill in the input ranges for the $x$ and $y$
as indicated above (that is, make $x$ correspond to $A3:A25$ and
$y$ correspond to $B3:B25$), select options to {\tt Plot Residuals} 
and {\tt Line Fit} and check the box for labels.  Press OK and a 
new ``sheet'' will be generated giving all the results of the regression.  
Note the tabs at the bottom of the grid saying {\tt Sheet 1} and
{\tt Sheet 2}.   You can toggle back and forth between the
main file and the regression results by pressing those
tabs.  
\item{$\bullet$}  We will discuss the {\it residual plot} in more
detail on Friday.  For now, the main thing that you need to 
know is that this plot shows the difference between the 
$y$-value from each data point, and the ``predicted'' $y$-value
for the corresponding $x$ given by the equation of the regression line
$y = mx + b$.  In other words the residual plot shows $y_i - (mx_i + b)$
for each $i$, $i = 1,\ldots,n$.
\item{$\bullet$}  {\it We will discuss the meaning of some of the other output later!}
\bigskip
\noindent
{\it Lab Work}
\bigskip
\noindent
Your goal today is to do as much as possible (hopefully all)
of the spreadsheet work for problems 17 and 19 from Chapter 4 on this
week's problem set.  (You will probably want to print out your
work in the lab and attach those sheets to the rest of the 
problem set.)  Use the graphing features of Excel
to produce the scatter plots in part a of each problem.
Ignore parts c in both problems.  (These
ask you to use the ``straightedge'' or ``eyeball'' method
to draw an approximate straight line fitting a data set.  



\end 
