\magnification=\magstep1
\centerline{MONT 102N -- Modeling The Environment Seminar}
\centerline{A ``Crash Course'' on Excel}
\centerline{September 26, 2011}
\bigskip
\noindent
{\it General Information on Excel}
\bigskip
\noindent
To  get ready for
work on this lab project and launch Excel in Haberlin 136,
\bigskip
\item{$\bullet$}  Log on the campus network with your
username and password.
\item{$\bullet$}  (This step can be done before
the lab.)  Launch gmail, look for an email
from me with three spreadsheet file attachments, and extract
and save them to your campus network $P:$ drive.  You will
be working extensively with those files for this assignment.
\item{$\bullet$}  From the desktop, double click the
shortcut for {\it Excel 2007} to launch the spreadsheet
program.
\bigskip
\noindent
Take a look at the overall layout of the of the Excel window.
There are tabs, menus, etc. similar to many Windows
programs, but there are some differences too.  In particular
note the large ``Office Button'' at the upper left.  This
is where all of the usual File options are now located (i.e.
the controls for reading in or saving files, printing, etc.)
\bigskip
Like all spreadsheet programs, Excel gives you a workspace
that is composed of a 2D grid of ``cells'' identified by
location -- by an {\it address}.  The columns are labeled
by capital letters, and the rows are labeled by numbers.
\bigskip
\item{$\bullet$}  A single cell is referenced by the
column, followed by the row, for instance $B23$ is
the cell in column $B$ and row $23$.
\item{$\bullet$}  A range of cells is referenced by
giving the ``starting cell,'' a colon, and the ``final
cell'' in the range.  For instance $B2:B45$ indicates
the cells in column $B$ and rows 2 through 45.  $B2:F2$
indicates the cells in row 2 and columns $B$ through $F$.
Similarly, $B2:D10$ indicates all the cells in a
{\it rectangular block} with upper left corner at cell
$B2$ and lower right corner at cell $D10$.
\item{$\bullet$}  The addresses seen so far are all
{\it relative addresses}.  In other words, they are
set up so that if we perform an operation in one
cell that depends on the entries to the left in its
row, then it is possible to copy and paste that operation
to other rows and the entries in the new row will be
used.  If you want to specify a {\it fixed} address
then put in $\$$ characters:  $\$C\$5$ means the
cell with fixed address in column $C$ and row 5.
(We will see several examples of this in a while;
if it is not clear why we need this distinction,
wait until you see the examples!)
\bigskip
The contents of a cell
can be a text label identifying what the data in a row or
column represents, a number, or a formula indicating how
to perform a desired calculation using other information
in different cells within the spreadsheet.  When you
finish entering a formula this way and press the Enter key,
the indicated computation
is performed and the result is displayed in that cell.
One {\it very nice} feature of spreadsheets is that
if you change the contents of a cell that is used to
compute a value this way, then the calculation is
automatically performed again to update the value displayed.
We will also see this in a moment.
\bigskip
\noindent
{\it A First Worked Example}
\bigskip
\noindent
Begin by reading in the spreadsheet file {\tt First.xls}
that you extracted from my email:
\bigskip
\item{$\bullet$}  Press the ``Office Button'' at the upper
left of the Excel window,
\item{$\bullet$}  then Open,
\item{$\bullet$}  Find your $P:$ drive in the folder box at the top
of the Open window, highlight the file {\tt First.xls},
\item{$\bullet$} and press Open at the bottom.
\bigskip
\noindent
You should now see a rectangular block of cells filled
with names, text, and numbers at the upper left of the spreadsheet in rows
1 through 10 and columns $A$ through $E$.  Think of this as
the grade book for a small class with 8 students (the rows are labeled
with their names) who
have had four assignments as in the labels for columns $B$ through $E$.
Note that $A12$ has
the text ``Average''  but there
are no numbers on that row (yet!).  We are going to use
Excel to compute the averages, on
each assignment.
\bigskip
\item{$\bullet$} In cell $B13$, enter the formula
{\tt $=$AVERAGE(B2:B9)}.  As you type, you will see
this showing up in the cell and in the input box
above the grid.  When you are done press Enter, and the average
will be computed and displayed.
\item{$\bullet$}  Now we will use the same method
to compute the average on each of the other assignments:
Highlight cell $B13$ by clicking the left mouse button
over that cell.  Make sure the Home tab at the top
of the Excel window is active, press Copy (next to the ``Office Button''),
drag the highlighting box so that all the cells in row
13, columns $B$ to $E$ are highlighted, and press Paste (next to Copy).
You should now see the averages for each column.
\item{$\bullet$}  In doing the averages we
were making use of the {\it relative addressing} mentioned above.
Copying the formula in one cell and pasting it into another
also changed the addresses of the cells that the formula
was applied to.  Now, we are going to perform an operation
where we want to use contents of a fixed cell on multiple
rows.  Start by filling in new information in row 14:
Put a text label ``Weights'' in $A14$ and the constants
$.3$ in $B14$, $.25$ in $C14$, $.4$ in $D14$, and $.05$ in
$E14$.
\item{$\bullet$}  In cell $F1$ add the text label ``Course Average.''
In $F2$ enter the formula
\medskip
\centerline{\tt =\$B\$14*B2 + \$C\$14*C2 + \$D\$14*D2 + \$E\$14*E2}
\medskip
\item{}  You should see the weighted average displayed.
\item{$\bullet$}  You can now copy and paste that formula to the other
cells in column $F$ and rows 3 through 9 to do the same computation
for the other students in the class.  (Note that the weights always
come from the same row, hence the fixed addresses.  Can you see what
would happen if we did not do it that way?)
\bigskip
\noindent
Other useful information
\bigskip
\item{$\bullet$}  In an Excel formula you can
square the contents of a cell by saying, for instance $B13$\^{}2.
\item{$\bullet$}  If you want to take the square root of something computed from
information other cells, you 
use $SQRT()$.  For instance, to compute the square root of $B13^2$ and 
$C13^2$, you would say
\medskip
\centerline{\tt = SQRT(B13\^{}2 + C13\^{}2)}
\end 
