MONT 102N – Modeling The Environment Seminar A “Crash Course” on Excel September 26, 2011

General Information on Excel

To get ready for work on this lab project and launch Excel in Haberlin 136,

• Log on the campus network with your username and password.

• (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.

• From the desktop, double click the shortcut for Excel 2007 to launch the spreadsheet program.

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.)

Like all spreadsheet programs, Excel gives you a workspace that is composed of a 2D grid of “cells” identified by location – by an address. The columns are labeled by capital letters, and the rows are labeled by numbers.

• A single cell is referenced by the column, followed by the row, for instance B23 is the cell in column B and row 23.

• 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 : F 2 indicates the cells in row 2 and columns B through F. Similarly, B2 : D10 indicates all the cells in a rectangular block with upper left corner at cell B2 and lower right corner at cell D10.

• The addresses seen so far are all 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 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!)

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 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.

A First Worked Example

Begin by reading in the spreadsheet file First.xls that you extracted from my email:

• Press the “Office Button” at the upper left of the Excel window,

• then Open,

• Find your P : drive in the folder box at the top of the Open window, highlight the

file First.xls,

• and press Open at the bottom.

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.

• In cell B13, enter the formula =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.

• 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.

• In doing the averages we were making use of the 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.

• In cell F 1 add the text label “Course Average.” In F 2 enter the formula

            =$B$14*B2 + $C$14*C2 + $D$14*D2 + $E$14*E2

You should see the weighted average displayed.

• 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?)

Other useful information • In an Excel formula you can square the contents of a cell by saying, for instance

B13ˆ 2.

• 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

                       = SQRT(B13^2 + C13^2)