Using Spreadsheets

Email me

Back to:

Introduction:

Note: If you are browsing this on a Windows machine and have a 15 inch monitor or larger, it may be easier to do this lab if you reset your monitor at a higher resolution (1024x768 is recommended). This will make the text smaller and more of the document (your spreadsheet) will fit onto the screen at one time. To do this, go to control panels, choose display, choose settings, and change the resolution of the display (both sliders) from the standard 800x600 to 1024x768. Click OK. Be sure to click OK again after the change has taken place so that the computer won't automatically revert to the previous resolution after 15 seconds.

This lab is a brief introduction to the use of computer spreadsheets. We will use the spreadsheet program available in the biology computer lab, MS Excel. However, there are more similarities among spreadsheet programs than differences, so, once you learn to use this one, you should be able to figure out how to use other programs. Just keep in mind that there is a way to do what you want to do and don't give up until you find it. This assumption has served your instructor well. If you use another spreadsheet system, you must save your spreadsheets twice. However, a problem may arise when you send the file to me. If my program can not open your document, we will have to work on a solution (usually a fix can be found).

This lab should be read when you are sitting in front of a computer with a spreadsheet program running. It was originally written for the MS Excel program, version 98, and was written on a Macintosh computer. However, most of it will apply to other versions of the Excel program and to MS Excel running within a Windows operating system. I have attempted to note where the Windows program differs from the Macintosh.  If you discover more differences, please email me when you do and I can alter this lab.

Spreadsheets are useful to biologists for myriad reasons. They can reduce the time it takes to do numerous, repetitive calculations. If an error is made in data entry and it is not corrected until after the formulas are entered and the calculations done, the spreadsheet will automatically update all of the cells whose values depend on the corrected data. In addition, once a set of results are calculated, they can be printed as a table or as a graph (the graphic capabilities of the program are the subject of another lab). The tables and graphics can also be incorporated into other documents generated by other programs, such as text documents produced by word processing programs. If you become a sophisticated spreadsheet user, you can use it to write programs to do complicated calculations (including statistical analyses) and report those calculations as charts and graphs. A good spreadsheet program is a very powerful tool for solving complex problems.

Usually, one adds the results of a spreadsheet to a word processor document (often as a table or a chart). This looks neater and offers other, more substantial, benefits. However, for the purposes of this class, you can include the answers to questions, conclusions, and other text into the spreadsheet file and submit your lab over the internet by sending me an email with the Excel file attached. Instructions on how to do this are given below. This is the easiest way to submit and get the corrected material and no paper is wasted. You will always have a copy of the file, in case it is lost (human error transcends all technology). One word of caution. If I can't find the answers, I can't give you the deserved credit. Organize the spreadsheet so that it is easy to read. This means that you should format before sending to me. Look at the overall organization? Are all of the answers in the same order as the questions in the lab? Is all of one answer in one place (or is there an instruction about where to go)? Have you changed the number of decimal places to make the spreadsheet more readable? If I find that you are not taking care, I will reduce your score, as it costs time to search through a sloppy lab for the needed answers. If you have questions about how to do this, come by my office and we will look at your file. Any skills you develop here will serve you well in many endeavors beyond TSU..

There are some other things that are necessities, though. The data must be entered and must be checked. You should calculate at least one value (two or more are better) by hand to check that the formulas you have entered are correct and you are getting the correct answers. In addition, logical checks should be done for the overall results. A good example of this not being done was produced in an earlier class by submission of a survivorship curve that went up over time. Survivorship curves start at 100% and can only stay the same or go down over time (think about it!). A logical check of the outcome would have caught this mistake. Look to see that things (like proportions) that should add up to 1 do, in fact, sum to 1.

A final word of introduction. This lab is meant to be a tool for learning. To make use of it, some exercises have been added to the text for the more complicated tasks so that you actually perform task. However, you should try the simpler tasks out in the spreadsheet as you read each section. Don't just read through a section, do a section. Make you own examples where there are none in the text. You must participate to learn.

Some Useful Terms:

• Menubar -- this is the top of the computer screen (or window in MSWindows operating systems) and has pull down menus from which you can choose options.
  • Many of the options have keyboard equivalencies next to them, so that you can use the keys as a short cut to avoid moving the cursor all around the screen.
• Cell -- A spreadsheet is a large document divided into cells.
  • Usually, the columns have letters and the rows have numbers so cell D190 is in column D (the fourth from the left margin) and row 190
  • Cells can contain either values (a number or some text) or they can contain formulas (although you only see the value that the formula calculates)
  • Note that newer versions of spreadsheet programs allow multiple worksheets within a single data file (called a workbook), so that you can have more than sheet open at a time. In MS Excel, these are called worksheets (each a separate page in your workbook). You start with three worksheets and can add more (under the Insert menu) more or delete some (under the edit menu). You can also add charts and graphs as separate worksheets in the workbook (file) or charts and graphs can be added as cells within a worksheet. We will most often do the latter.
• Cell reference - this is the tag that labels a cell. Cells are always referred to by the column they are in and then by the row they are in. Columns are labeled with letters and start with A at the left edge of the spreadsheet. When you get to column Z, the 26th column, the next, the 27th column, is column AA. You can go all the way to column IZ or so. Rows are numbered starting at 1 at the top. You can have up to about 16,000 rows (many more than columns). So cell D8 is in the fourth column from the left and down 8 rows.
• Command line -- the command line is displayed at the top of the spreadsheet (in Excel) and is where you tell the program what to display in the selected cell or cells in the spreadsheet below. Note that, if you enter a formula into a cell, the value appears in the spreadsheet, but the formula appears in the command line.
• Formula -- this is a mathematical or logical formula that, when entered in a cell preceded by an equal sign, returns the value of the formula to the cell.
  • If you enter into a cell 6 / 3, you will see 6 / 3 because the program sees this expression as text.- Try it!
  • If you enter into a cell = 6 / 3, you will see 2 in the spreadsheet and = 6 / 3 in the command line above - Try it!
  • the difference between the above is simply the equal sign. Note that it must be in the first space. If it is not(if, say, a space is there), then the program will see the formula as text.
• Toolbar -- a bar in the window with icons that act as buttons. Just push on an icon and you have a shortcut to a command. The only drawback is that you have to know what the icons mean
• Workbook and Worksheet -- the MSExcel programmers have chosen to call a MS Excel document a workbook. There is a reason for this. If you look at the bottom of a workbook, you see tabs (with the names sheet 1, sheet2, etc. Three are the default). Try clicking on one of the tabs. You switch to another clear table, called a worksheet, as if a document were a notebook with lots of blank sheets. Thus, one document can have many tables in it, each with its own name (you change it by selecting Sheet 1 and typing in whatever you please - Try it!)

What is in a cell:

The cells in a spreadsheet have one of six things in them -

• nothing (which is different from either a space, which is text, or 0, which is a number)
• text
• numbers (integer or real)
• logical values (true, false)
• dates
• times

All cells have a value (even text). The value might be numerical or logical. The value of a cell can be directly entered, or can be the result of a formula (and the value calculated from the formula can depend on the value of other cells) How a numerical value is displayed is called the format. For example, 16.25 can be 16.25, $16.25, 1625%. or 1.625E1 depending on which format style (general, currency, percentage, scientific notation) you choose for that cell. In addition, there are special numerical formats (text, time, date, logical) that have different properties than numbers. For example, logical values are all 0 or 1. Dates come in day-month-year format. What you can do with a cell depends on what is in that cell. If you multiply a number times a date, the result may not be what you expect. There is usually no problem if you are calculating with cells in any of the numerical formats, but mixing formats can produce error messages or unexpected results.

There are also general formatting options that apply to all types of values (including text). You can choose the font, size, justification, character color, cell color, etc. of the what is displayed in the cell. Remember that text will be printed over adjoining cells as long as there is nothing (as defined above) in that cell, but as soon as something is entered in the overwritten cell, then the text is truncated at the edge of that cell. So, when writing an answer a question, you will see the entire sentence or sentences in the spreadsheet as long as you are overwriting cells with nothing in them.

The Importance of UNDO:

Pull down the EDIT menu on the Menubar. and look at the top choice (which will probably be disabled, since you have not yet done anything). It is the undo option. VERY VALUABLE. It will undo you last action. Use it. In the more recent versions of EXCEL, it will undo multiple actions, one at a time in reverse order - Try it! It has saved the author of this lab on more occasions than he can comfortably admit to.

Controlling the Display - Re-sizing columns and rows, controlling decimals, and splitting screens:

The following topics deal with how to manipulate a spreadsheet. You may want to change one or more cells. You may want to change a row or columns size. Finally, you might want to see the edges of an area too big to fit onto your computer screen. Here is a description of how to do those things. To select a cell, click on it. To select a few cells, select one on a corner of the area you want and drag the cursor to the opposite corner before releasing the mouse button. To select a row or column, move the cursor to the left side or top of the worksheet and click on the cell with the row number or column letter in it. To select all of the cells, click in the blank space at the upper left of the worksheet (where the row and column labels intersect).

Cells can be resized simply by selecting the columns or rows in which the cells lie. Once you have selected the columns or rows you want to resize (this works for entire rows or columns only) and moving the cursor over the row number (left edge of the spreadsheet) or the column letter (top of spreadsheet). The cursor will change from an arrow to a line with double arrows. With the cursor showing this display, simply click and drag to change the size of the selected columns or rows.

Often, assignments are turned in with the results displayed as numbers with up to 10 places in the decimal. This is a precision that is not needed and makes the results difficult to read. You can control the number of decimals displayed and printed by changing the format of the cells. Look at the toolbars. If there is are a pair of icons there that have two rows of zeros, these icons will change the decimal places of selected cells when you click on the icon. If there are no icons, pull down the format option on the menu bar and choose numerical format. A dialog box will open and you can set the number of decimal places displayed here. Notice that this does not affect the actual value of the cell, only what is displayed. Change it to 0, 1 or 2 as greater precision is rarely needed. A second way to do this may be found in a toolbar at the top of the document. If one of the tool icons is a dot with two zeros over a dot with one zero and an arrow, this can be used to reduce the number of decimal places displayed by selecting the entire range of cells to be changed (the upper left most cell must have a decimal value in it) and click on the tool icon until the desired number of decimal places is displayed. The adjacent tool will increase the number of decimal places. PLEASE HAND IN ASSIGNMENTS WITH THE APPROPRIATE NUMBER OF DECIMAL PLACES PRESENTED. IT IS MUCH EASIER TO READ.

Tables often get to be too large to fit onto the screen. However, it is possible to get the edges of a large table on the screen at the same time. This is useful if you want to change an entire row or column at the same time. You do this by splitting the screen. Look at the slide bars on the right and bottom sides of the window. There is a black region at the end of the bar. Move the cursor over it and the cursor changes to a pair of parallel lines with double arrows. Click when the cursor has changed and drag and you will split the screen either horizontally or vertically. Notice that the slide bar is also split. You can move the split screens independently, so you can have the top left corner of a spreadsheet in the upper left area and the bottom right corner in the lower right area. You should try splitting the screen now, even though you have not entered anything yet. You can get rid of a split by clicking down on the black region, moving it to the edge of the document while holding the mouse button down, and releasing the button once the black bar has reached the document's edge.

Relative versus absolute references:

There are two ways to enter a cell's value into a formula in another cell. We will do this soon, but first we must introduce the distinction between the two kinds of cell references (a cell reference is the form in which a cell appears in a formula). Which type of reference you choose depends on which one you need. A cell is identified by its reference:

• relative references (the usual) like N16, or AA143
• absolute references like $N16, or N$16, or $N$16

Relative references change in a predictable fashion as you move from one cell to another. The dollar sign denotes that the reference is absolute and does not change. You can change the type of reference simply by clicking on a cell, moving the cursor to the command line and clicking it into the formula at the point you wish to change. Then simply add or delete the $. You will see below, that, when copying a formula into a different cell, you have to keep track of the difference.

• When copying formulas from one cell to another, relative references will change as the rows and columns change
  • if you copy the formula =C3 + 2 from cell E3 to E4, then the formula would change to = C4 + 2 (because you went one row lower but did not change columns)
  • if you copy the formula =C3 + 2 from cell E3 to H3, then the formula would change to = G3 + 2 (because you went four columns over but did not change rows)
  • if you copy the formula =C3 + 2 from cell E3 to H4, then the formula would change to = G4 + 2 (because you went one row lower and four columns over)
  • if you copy the formula =C3 + 2 from cell E3 to A4, then the formula would be nonsensical because you have moved four columns to the left, but the reference would have to move four columns too, but you can't go past A! (Try it and see.)
• When copying formulas from one cell to another, absolute references will not change as the rows and columns change
  • if you copy the formula =$C3 + 2 from cell E3 to H4, then the formula would change to = $C4 + 2 (the row number changes because you went one row lower and it is a relative reference but the column is an absolute reference, so it won't change no matter how you change columns)
  • if you copy the formula =$C$3 + 2 from cell E3 to H4, then the formula would not change (it would still be = $C$3 + 2)
  • if you copy the formula =C$3 + 2 from cell E3 to H4, then the formula would change to = G$3 + 2 (because you went four columns over and the rows will not change)

Entering a Formula:

One of the things you can do is to calculate a result from the values in one or more cells according to a formula. Remember that the standard symbols for spreadsheets are thus: - is the hyphen and is used for entering negative values and for subtraction, * is multiplication, / is division (or multiplying the value to the -1 power), ^ is for exponentiation (=25^0.5 in the command line returns the value 5 in the spreadsheet, the square root of 25). Try entering a formula in a spreadsheet. To do this, you will have to keep in mind the difference between the cell's value and what is entered on the command line. If you select a cell whose value is calculated from a formula, you will see the value in the cell but the formula on the command line.

We will use an equation for a line, y = mx + b, this will calculate a y for any given x, as long as you know the slope (m) and the intercept (b)

• Pick a cell (A1 is a good choice) and enter in Slope = by clicking the mouse in the cell and typing the text (notice that it appears in the command line too)
• in the cell next to it on the right enter 2.29 by clicking the mouse in the cell and typing the text (notice that it appears in the command line too)
• In the cell below the cell with "Slope =", enter Intercept =
• in the cell next to it on the right enter 3.44
• Now create a column of X values somewhere else on the spreadsheet (cell D3 is a fine choice)
  • enter X = in a cell
  • in the 5 cells below it, enter the values 2, 4, 9, 12, and 22
  • enter Y= in the cell to the right of the cell with X= in it
  • now you are ready to enter a formula that calculates y as a function of x
    • in the cell to the right of the one with 2 in it, enter = and leave the cursor blinking right there
    • click on the cell with 2.29 in it. Notice that you see the cell reference in the command line and not the value of the cell.
    • now type * (its over the 8 on your keyboard) - this is the multiplication sign
    • now click on the cell with 2 in it. This gives you the "mx" part of the formula for a line.
    • If you are using a Windows machine, type in a plus sign (+)
    • now click on the cell with 3.44 in it. This gives you the "b" part of the formula for a line.
    • now hit return (Mac) or enter (Windows)
    • DO NOT ENTER THE FORMULA BY ENTERING THE VALUES INTO THE FORMULA (SO THAT IT LOOKS LIKE THIS: "=2.29*2+3.44") AS THIS WILL ELIMINATE THE POSSIBILITY OF TAKING ADVANTAGE OF THE MOST USEFUL, TIME-SAVING ABILITIES OF A SPREADSHEET.

• you should see 8.02 in the cell with the formula and the formula itself in the command line. Notice that the formula has a reference to the cells upon which you clicked. You can also enter cell references by simply typing them in the command line.
• We will calculate the rest of the x values next.

If you make a mistake in entering a formula, you get one of the following error messages

• #name? -- you have used a name in the formula with which the program is not familiar
• #num! -- you have attempted to compute an impossible or imaginary number (like the square root of a negative)
• #value! -- one of the arguments in the formula is wrong (outside the acceptable range, e.g. a matrix is expected but a scalar is given, etc.)
• #div/0! -- you have divided something by zero
• #ref! -- the program does not understand a reference to a cell in the formula

Copying formulas:

One of the most useful capabilities of a spreadsheet is its ability to do repetitive tasks, such as performing the same calculations on an array of data entries. This can save massive amounts of time. However, you must apply what you have read above (especially how to enter a formula and keeping track of absolute and relative cell references) in order to get the correct results. Let's use the data entries from above and the formula you calculated before to illustrate copying formulas.

1. Go to the cell with the formula for y. When you click on that cell, you will see the value in the cell and the formula in the command line. Suppose you want to apply the formula to the other cells in the X series. Not a problem.
2. Click on the cell with the formula for y (the first cell under the cell with Y =). Choose copy from the EDIT menu.
3. Click on the next cell down. (Notice the marching ants indicate the cell from which the formula is to be copied.) Choose paste from the EDIT menu. Hit return (Mac) or enter (Windows).

Oops - you got the wrong answer. Go ahead and check. Now click on the cell with the formula for y (the first cell under the cell with Y =) again. Look at the formula. Both of the constants (the slope and the intercept) are wrong, but the X value is correct. Both the correct and incorrect parts are due to relative references. You changed the row reference when you copied the formula into the same column but one row down. All of the cell references are one row down. OK for the X value, as you wanted the next one down, but you wanted the same cell references for both the slope and intercept. We need to correct this with some absolute references.

• Click on the cell with the original formula for y (the first cell under the cell with Y =). Look at the command line. You need to put a $ in the formula before the number that indicates the row for the intercept and for the slope. Do this by clicking the cursor into the command line at the appropriate place (you can use the arrows to move left and right if you need to move the cursor), typing the $ character, repeat for the other cell reference to change, and hit return.
• Now go back and repeat the steps above from 1 to 3.

Check that the formula is correct. Once you verify that it is, you can copy the formula into the remaining cells by repeating steps 1 to 3 but, instead of selecting a single cell to copy the formula into, select multiple cells. Then paste as directed before. A second way to copy a cells contents into adjoining cells (in a row or column) is to:

• click on the first cell you want to copy. Hold down the control (apple) key (Mac - I seem to remember its the same key on Windows, but that may not be so) and then click on the last cell of the series you want to paste the value into (the cell with 20 here). You have selected all of the cells in between. Then choose copy down (or over, whichever you need) from the edit menu or hit the keyboard equivalents (listed next to the commands in the menu).
• a second option is to click on the cell you want to copy and hold the mouse button down. Drag the cursor to the last cell you want to copy into and let go of the mouse button. You have selected all of the cells in between. Then choose copy down (or over, whichever you need) from the edit menu or hit the keyboard equivalents (listed next to the commands in the menu).

The second method is useful when you have all of the cells on a single monitor screen. The first is useful if you have split a screen and the copy-from cell on one split and the last copy-to cell in the other split.

Using Functions:

some complicated formulas that everyone uses are included as standard functions.

• to get a function, click on a cell and pull down the insert menu from the menu bar and select the function option
• when you release the mouse, you get a dialog box. Most of the functions you want are under the Math & Trig or the Statistical option
• each function has arguments that you have to enter. We will illustrate with the AVERAGE function
  • click on a cell, get the function dialog box, and choose Statistical functions, then choose AVERAGE from the menu and you get a dialog box
  • notice that there is a description of the function and a description of the necessary arguments in the bottom of the dialog box
    • click OK and a second box opens up so that you can enter the data to average
    • it is important to know that, if this box is in your way, you can click on it and drag it out of the way
    • for the average function, you can enter a list or an array. A list it useful if the numbers are not all in adjacent rows and columns and the array method is better if they are.
      • a list is a series of numbers or a series of cells with numbers in them (even if the number is a result of a formula) separated by commas
        • to enter a series, be sure your cursor in in the line of the dialog box for the entry of the argument and click on the cells with 2, 3, and 5 in them while holding down the apple key (use the Cntl key for Windows).
        • you can enter a values that are not in an array (not contiguous) in this fashion
      • an array is a series of cells in a row or a column or a square (rows x columns)
        • to enter an array, be sure your cursor in in the line of the dialog box for the entry of the argument and click on the 2 and, without letting the button up, drag the cursor to the 20 and release
        • you see that the first and last cells are entered with a colon separating them. This indicates an array with the dimensions given by the cells entered
        • You can mix arrays and numbers in a list, e. g. the entry A4, A7, B3:B8, D4:E6 would average the values of the cells A4, A7, B3, B4, B5, B6, B7, B8, D4, D5, D6, E4, E5, and E6

A formula can have a reference in the formula to a function or another cell in the spreadsheet (even if on another worksheet in the same document, which Excel refers to as a workbook).

Entering a series:

It is often necessary to enter a series of numbers in a row or column. Usually the numbers go up by 1 each time, but that isn't the only possibility. This is an easy two-step operation.

• To enter a series, enter the first value of the series in a cell and select the rows or columns you want to hold the series (starting with the first value in the first cell of the series).
• Pull down the EDIT menu, select the FILL option, which produces a sub-menu.
• On this submenu, choose the SERIES option. (On older versions of EXCEL, the SERIES option is on the DATA menu on the menubar.) This brings up a dialog box. You have to set the step value (the difference between successive values in the series), the type of series (usually linear). Usually, the default values are 1 and linear and you can just click OK. However, you can enter other values (the series can go up by 3 or down by -1). I have not tried to enter a formula in it, but you could try entering a formula here (this would make this function very useful at times!)

Sorting rows and columns:

Another valuable time-saver is the ability to sort multiple rows and columns by text or numerical order.

• To do this, select the area you want sorted. The row or column that contains the actual values to be sorted must be included in the overall area selected. Choose the sort option from the DATA menu on the menubar.
• If you have selected an area with either values or text in adjacent cells, the program will ask you if you want to include these adjacent cells. If you do, you will have to go back and reselect. If not, click continue and you get a second dialog box.
• In the second dialog box there are several options. The default options are that you want to sort by the left-most column of the area you selected and that there is no header row (a header row is the row at the top and is not sorted). If all of this is fine, click OK. If you want to sort by rows or by more than one column or have included a header row, you will have to change the options.
• Usually, when you have several columns in a table and you want to sort one of the columns, you want the entries in the other columns to be in the same order as the sorted column. Try it this way
• Enter this small table of names and ages:

  Ralph	25	M
  Tony	44	F
  Leslie	31	M
  Kate	19	F

• Try sorting your table from the youngest to the oldest. Select the entire table, choose sort, choose the second column (which letter this is depends on where you put your table) and click OK. You should get this:

  Kate	19	F
  Ralph	25	M
  Leslie	31	M
  Tony	44	F

• Undo the sort (good undo practice) and then select only the age column and sort it. Notice that the names and sex designations are now not paired with the correct ages.
• Sometimes, it is necessary to sort by more than one column. Say you added a third column to the table above, one with male or female, and you wanted all of the females listed from youngest to oldest and then all of the males. You would have to first sort by the third column and then by the second, so all of the males would already be separated from the females before each group was sorted by age. To sort by more than one column, enter the second column in the second THEN BY space. This will first sort all values by the first column. If there are multiple identical values in the first column, they will be sorted based on the values in the second column. You can do this for up to three columns. (Try it and see.)
• To sort by a row, click on the option button. This will get you another dialog box. Change the order from sort top to bottom to sort left to right , then click OK. Notice that Column A has changed to Row A in the original dialog box.
• Click OK.

The sort function will sort the rows or columns you selected according to the key rows or columns. The rest of the selected row or columns will "tag along", so that adjacent cells are still adjacent after the sort. This can save a lot of time when doing something in which you have to know the median value (the midpoint).

Problems:

How to lose points when doing problems: Fail to label you results! It is not really fair to make the grader search for your results. So, it is you responsibility to clearly label results and present them in an orderly manner. If you calculate a sum in a cell, then label an adjoining cell with the fact that the sum is there. Do this for all of your results. THE PURPOSE HERE IS TO LEARN HOW TO USE THE PROGRAM TO SPEED WORK. THIS IS DONE THROUGH FUNCTIONS, CELL REFERENCES, ETC. THE PURPOSE IS NOT JUST TO GET THE RIGHT ANSWER BUT ALSO TO GET IT IN THE RIGHT WAY. USE FUNCTIONS WHERE POSSIBLE, NOT HAND CALCULATIONS, ETC. FOR INSTANCE, IF AN AVERAGE IS REQUIRED, I WILL CHECK TO SEE THAT THE AVERAGE HAS BEEN DONE THROUGH THE USE OF THE AVERAGE FUNCTION. IF YOU ADD THE VALUES UP AND DIVIDE BY N YOURSELF, YOU HAVE AN INCORRECT ANSWER BECAUSE YOU DID IT THE WRONG WAY.

ALL DECIMALS SHOULD BE TWO PLACE DECIMALS. ADJUST THE FORMAT ACCORDINGLY

1. Copy down one extra cell in the exercise on entering a formula in the lab above (copy one cell past the 22). You get an answer of 3.44. Why is this so? Explain why this cell has the value of 3.44.

2. Enter a matrix of the following values and calculate columns with the following formulae in them (This will involve the use of parentheses in your formula. Remember, operations inside parentheses are done first):

• x plus 2 times y (no parentheses)
• x plus 2 then times y (you will need parentheses for this - compare it to the first formula and notice how different the results are)
• x times y divided by z
• add x and y then divide the sum by z. To do this you will need to use parentheses.
• do the above calculation without parentheses (just to see what difference it makes)

3. Using the matrix you entered in Question 2, make new columns and rows (as appropriate) with the following values in them

• square root of x
• natural log of y
• sum of all x's, sum of all y's, sum of all z's.
• Average x, average y, average z
• maximum x, maximum y, maximum z (I know this is easy to see, but you must use the function. I will check.)

4. Enter the matrix below. Leave at least one column blank on the left hand side.

Copy the entire matrix and paste it adjacent to the original but to the right. Notice that columns A and C have repeating values but they are scattered. You can sort them and get all grass, then all shrub and then all tree. However, the trick is to do the sorting without losing the relationship among the columns, so that the first "Desert" has a column B value of 29 and a column C value of "fire plot" after the sort as well as before it. Notice again that within each of the column A categories, column C can have repeat values (the four tree entries have two fire plot values and two control values). Your job is to sort the copy of the matrix by column A as the first sort and and by column C within column A as the second sort.

• Label the sorted columns Sorted A, Sorted B, Sorted C.
• Sort the three added columns by both A and C (A as first key, C as second, both ascending)

Secondly, we can sort for purposes other than easy viewing. The median value is the value in the middle of a series of numbers, when the numbers are sorted from smallest to largest. You can find the median easily with the sort and series functions. First you will find the number of rows and the middle row with the series function and then sort column B so that the middle value in the column can be identified (this is the median)

• Enter a 1 in the column to the right of the first row with data. Select that cell and all of the cells below it to the bottom of the table. The program will enter the numbers from 1 to the total number of rows in the selected cells if you now choose the series tool.
• Copy column B from the original matrix you entered and paste it to the right side of the column with the series you just did..
• Label the new column "For Median of B"
• Sort the column labeled For Median B and then indicate what the median of variable B is.

Medians are known in statistical jargon as "measures of central tendency in a set of values." There are other measures of central tendency (many more), but the mean is the most commonly used other "measure of central tendency." Each measure has its own properties and may be very different from other measures. We can illustrate this point by:

• Calculate the mean of variable B using the average function (in the statistics group). Now look at the two and answer the following question. In a factory with 100 workers and one boss, the workers all get $100 per week and the boss gets $100.000 per week. Would you rather receive the median wage at the factory or the mean? Why?
• Be sure to use the average function for calculating the mean. Remember that I can see the formulas in the formula menu bar, no matter what value is displayed in the cell.

5. X is a variable that can have the values 13, 27, 122, 35, 33, 84, 105, and 52.

Define what i and n mean in the summation formula below and give their values for this exercise (x is the variable defined above).

Show me, through the use of a spread sheet, that the inequality below is true when x takes the values given in the first line of this problem. To restate this, I want you to demonstrate that the sum of a series of numbers squared is less than the square of a sum of numbers (these expressions occur often throughout statistics and biology) for the values of x given above. Once again, the xi refers to the variable values defined above in the first line of this exercise.

6. Fill in the chart.

NOW, LOOK AT THE BEGINNING OF THE PROBLEMS AND READ WHAT IS THERE. CHECK TO SEE THAT YOU HAVE DONE WHAT IS REQUESTED THERE (FUNCTIONS? FORMAT?).

How to submit this assignment:

The assignment will be submitted through the internet. It will not be accepted in any other format as there is no way to check formulas unless I have the file on my computer. A printout of this file will only give me the values that result from the formulas, and I want to see the formulas (useful when you get something wrong). I will not put your floppies into my computer, as I am not sure how good my virus protection is. This is how to do it.

• Save the EXCEL workbook with your answers by clicking on the save tool (the icon looks like a diskette) or choosing Save under the File menu. Give it a name using the standard formula for this class: Lab 1 XxxxYyyy, where the Xs are the first four letters of your LAST name (first capitalized, the rest small) and the Ys are the first four letters of you FIRST name (once again, first capitalized, the rest small). Make sure Lab is Lab and not LAB (this is so strict because it is much easier to organize your labs if the names are all in the same format). Remember where you saved the file.
• Select the mail function on you internet browser or click on this: Email me. If you selected the browser function, then enter my email address in the TO: line. It is pganter@tnstate.edu.
• Enter "Lab 1 XxxxYyyy" (see above) into the line for SUBJECT:
• If you are mailing from somewhere other than the email account you want me to use, include that account in the message, I can't send your corrected assignment to the right place unless I know the correct address, otherwise I will send it to the same account from which it was sent. You will get an email back with the corrected file. The file will have the grade as part of the name. Please write a short message indicating what you found useful and what needs to be changed in the lab.. Be sure to mention errors and areas where you had difficulty.
• Click on attachments (where this is located depends on the mail program you use). A dialog box will open which will allow you to find and choose the EXCEL worksheet with your answers. Click add and, afterwards, check to see that your file is listed as an attachment in the header of the email.
• Click on send and wait for confirmation that the message has been sent.

If you can not use the email function, there are multiple possibilities for the problem. The settings for email may be incorrect in the browser or there may be no email server associated with the terminal you are using. If you are in the computer lab in the basement of McCord hall, the problem seems to be the wrong preferences are set in the browser. The following instructions may correct the problem if you are in the Computer Lab:

• Fix number 1
  • Try opening a Hotmail account (http://www.hotmail.com)or a yahoo mail account (http://www.yahoo.com) and sending the file to me using the instructions you find there. You can do this from the terminals in McCord hall (computer science terminals)
  • Remember that you will be sending the email from the hotmail or yahoo website, so you need to go to those sites in order to set up or use an email account with them. It doesn't take long, just follow the instructions carefully and remember to attach your homework file and to use the file names and subject line instructions above.
  • Remember to tell me if you want me to return the corrected file to the hotmail address or to some other address (home, etc.).
• Fix number 2
  • Fix number two has been eliminated, as it will no longer work on our servers at school. If you can't send the email from the computer lab, ask the helpdesk to aid you.

If the above does not work (if you never see the message "mail sent"), you will have to save the workbook on a diskette or other removable medium (a zip disk, for instance). You will also have to copy down the instructions above or print the lab (or open this web page on a computer that is able to send email). Go to a terminal with email capabilities, put in the diskette, open the web browser software (Netscape Communicator/Navigator or Microsoft Internet Explorer) and follow the instructions above.

Last updated August 25, 2006