Lab. 1. Orientation in software: Microsoft Excel

   You need a diskette to do this lab.
   Don't forget to save your work regularly because the system may crash any time,
   and you may loose everything you have done!

   1. Open the Microsoft Excel window. Go through the Tutorial: select Help/Examples
   and Demos. Learn: Entering data, Creating formulas and links, Editing a
   worksheet, Formatting a worksheet, Creating a chart, Formatting a chart. If your
   computer has not enough memory to run tutorials, then use Help/Contents/Using
   Microsoft Excel; then go through Essential skills, Creating charts, Solving
   problems... [Using Solver, Statistical analysis of data].

   2. Enter data into column A: 1.4 4.6 10.7. Edit data: change 1.4 to 11.4; change
   4.6 to 5.6 Save data on your diskette: (a) insert a diskette (format it if it was
   not formatted before), (b) go to File/Save, (c) select the diskette (in the
   desktop), (d) enter the file name, (e) save.

   3. Select a row (use the row button), a column (use a column button), a
   rectangle. Delete all data (select it and then delete). Delete the worksheet.
   Retrieve data from the diskette (File/Open...). Select 3 cells, copy them to the
   end of the column. Copy the first column to the second column, and to the third
   column. Select a rectangle (matrix) A1:B5, cut it and paste it into E1.

   4. Delete all data. Put an arithmetic series 0, 1,..., 20 into column A: enter
   first 2 numbers, select them and then click on the small square at right-bottom
   of the selected block and drag it down to the cell A21. This operation is called
   autofill. Enter the equation =1/(1+exp(10-A1)), which is a logistic equation,
   into B1. When entering the equation, instead of typing A1 click with a mouse in
   cell A1 (this makes writing equations faster). Click on the small square at
   right-bottom of cell B1 and drag it down to the cell B21 (autofill). Click on any
   other cell in column B to see the equation.

   Note: How cell references are automatically updated in these equations? What is
   the rule of equation change?

   5. Modify the equation in cell B2: =1/(1+exp(10-$A$1)). Do not re-write the
   equation, but edit the existing one! Expand this equation to the entire column in
   the same way as before. Select any cell in column B and check if the equation is
   different from that in cell B1.

   Notes:
     * Dollar-sign in the cell reference prevents the change of this reference.
     * Dollar-sign can be used before the column letter, or before the row number,
       or before both.

   Now restore the original equation in the entire column B: make Edit/Undo. This
   will undo the autofill operation (note that you cannot make Undo for 2
   operations!). To restore the equation in the first cell, select the second cell
   in column B and autofill it up to the cell B1. Check the equation in cell B1 if
   it has been restored.

   6. Tables. Insert a row at the top of the spreadsheet and put column names there:
   "Time" for column A; and "Population density" for column B. Change font in all
   cells to Times New Roman, 12 pt. Select 2 cells with column names. Put a line at
   the top and a double line at the bottom of this row (Format/Cells/Border). Also
   but the line at the bottom of the table. Select the first column. Change the
   format of numbers so that all numbers will have 1 decimal digit after the dot.
   Select the second column. Change the format of numbers so that 3 digits will be
   shown after the dot.

   7. The title "Population density" does not fit into the cell. Select both column
   titles and format its alignment (Format/Cells/Alignment). Center it horizontally
   and vertically, use word wrap. Then change the width of the first row: click
   between row buttons at the left and drag. If necessary, change the length of
   columns in the same way. Now we can copy this table into the Microsoft Word
   document. Open Microsoft Word, select the table in Excel and copy it to Word. You
   can edit the table in word: change font, line spacing, borders, alignment, etc.

   8. Graphics. Select columns A & B and click on graphics (magic stick). Select an
   area for the graph by dragging the mouse in the worksheet. Select "scatter" graph
   with lines. Go through all menus, name the graph: "Logistic curve", use first
   column as the X-axis.

   9. Modify the graph: double-click on it. Then double-click on the line, on axes,
   etc. Change line width, style and color, change markers, remove legend, etc. Go
   through all menus of editing. Resize elements within the graph.

   10. The next step is to introduce parameters into equation. First, write the
   title "Parameters" in cell C1. Then enter parameter names: "maximum", "slope",
   and "center" in cells C2:C4 without quotation marks. If the graph covers these
   cells, then move it down with the mouse. Name the cells D2:D4 as "maximum",
   "slope", and "center", respectively. To name a cell select this cell, then go to
   Insert/Name/Define. The name will be suggested by the Excel; if you agree, click
   OK; if not, you can modify the name.

   Notes:
     * You can name not only cells but also rows or columns!
     * Don't use single or double characters as names (e.g., "a", "bb", "b1")
       because these strings are reserved for column references or cell references!
     * A shortcut for Insert/Name/Define is Ctrl+F3 (on PC) or Apple+F3 (on the
       Mac). Try it, it works much faster!
     * If you select a cell with a name, then this name will appear in the field to
       the left from the equation editing field.

   Put numbers 1, 1, and 10 into cells D1:D3 (which are named). Click on cell B1 and
   modify the equation as: =maximum/(1+exp(slope*(center-A1))). Autofill this cell
   to the entire column B to modify all equations. The graph should remain the same.
   Now modify parameters in cells D1:D3 and view the results. Change parameters in
   the following ranges: maximum=1:10; slope=0.2:5, centert=0:20. How each parameter
   affects the graph?

   11. The next step is to fit our logistic model to some real data. First we need
   to free some space for entering our data in the spreadsheet. Select cells C1:D4
   (block of cells with parameters) and move them to the right by 2 columns. To move
   a selected block, click on its thick border when the cursor changes its shape to
   an arrow, and drag the block to a new position.

   We will use the following data of the increase in gypsy moth counts in pheromone
   traps in a series of years in the area that was initially uninfested:
   0 1 2 1 0 5 10 8 30 70 150 300 400 800 900 1100 900 1000 1050 1200 900
   You need to enter these numbers into the column C. This can be done by entering
   these numbers by hand, but it is nice to learn some computer tricks how to enter
   these data automatically. Copy the numbers above and paste them into a new MS
   Word document. Then open the Edit/Replace dialog box, put the space-character to
   be found and be replaced by a paragraph mark. To enter the paragraph mark click
   on the button "Special". Then select "Replace all". Now all numbers will appear
   in a column. Select all (Ctrl+A or Apple+A) and copy. Then go to the MS Excel,
   select cell C2, and open Edit/Paste-Special dialog box. Paste as text. Now all
   should be done. Make a header for column C: "Data" and format it if necessary. In
   cell B1, change the title to "Model".

   12. Now we need to update our graph so that it will show both the data points and
   the model curve. Select the area A1:C22 and plot the scatter graph. Edit it so
   that data points are shown by markers, and the model is shown by line. To make
   the model closer to data, modify model parameters as follows: maximum = 800,
   slope = 0.5, center = 8. The result should look as follows:

                                      [lab1a.gif]

   13. The next step is to adjust model parameters so that the model will be closer
   to data points. We will use the least square method which is minimizing the
   squared difference between the data and the model. Estimate squared differences
   in column D. Enter equation =(B2-C2)^2 in the cell D2 and autofill it to the end
   of the column. Sum all numbers in column D: select cell D23 and click on the
   "sigma" button. Excel automatically selects the block for summation. If this
   block is not correct, you can change it using the mouse. Then click OK (green
   check button near the equation field). Now we need to modify model parameters so
   that the sum of squares (cell D23) is minimized. When the cell D23 is selected,
   click on Tools/Solver in the menu. If Solver is not shown in the menu, you need
   to go to Add-Ins and to install the Solver tool. If Solver is present but is
   grayed, it means that you are in the process of editing a cell, or in some other
   activity incompatible with running the Solver tool. In this case, finish editing,
   click on the cell D23., and then try using the Solver again.

   When the Solver dialog box appears, check that cell D23 is the target cell. Click
   on the "min" radio button so that the target cell will be minimized. Then click
   on the field "By changing cells:" and select 3 cells that contain parameter
   values with the mouse. Then click "Solve" and watch how the sum of squares is
   reduced in the left bottom corner of the Excel window. After several iterations,
   the Solver will stop. Accept the solution (press OK) and see how the graph has
   changed. Does the model fit better to data points now?

   14. In the menu select File/Print Preview. Use setup to change page orientation.
   Change margins and preview again. Go to Setup and delete grid lines. Print the
   graph.

   15. Excel is mostly used for making budgets. Delete all previous data, and
   develop a budget for your future project. Imagine that you are a Principal
   Investigator and your position is Assistant Professor, 9 months appointment with
   a base salary of $35,000. Use form CSREES-55 as a pattern to create your budget
   in Excel spreadsheet. You need to put salary for yourself (3 months), include
   fringe benefits (24% of your salary), pay salary for a GRA ($11,200 per year),
   pay tuition for the GRA ($4,200, put it into "Other direct costs"), add any other
   costs you want. Specify indirect costs as 14% of total direct costs. Use
   equations to estimate percentages and to sum numbers. When the budget is ready,
   try to modify some entries. The table will be updated automatically. Print your
   budget.

   16 The next exercise is to plot a 3-d graph (a 2-dimensional normal
   distribution). You will learn how to use dollar-sign in cell references to
   prevent the automatic update of either row number or column letter. Delete
   everything in the spreadsheet. Put numbers 0:20 in the column A2:A22; put numbers
   0:20 in the row B1-V1 (cell A1 is empty). Put equation
   =exp(-(($A2-10)^2+(B$1-10)^2)/20) into cell B2. Note dollar-signs ($) which make
   a column or a row constant (not-adjustable). Autofill this equation in the column
   B2:B22. Then, autofill the entire column B2:B22 to the right up to column V. Look
   at the numbers. Do they look like a 2-d normal distribution? If not, check the
   equation. Look at equations at different cells and check if the dollar sign
   works. Plot the matrix B2:V22 as a 3-d surface. Double-click on the graph so that
   it will have a thick border. Then go to print preview, and finally print the
   graph. The spreadsheet will not be printed.

   17. In the following exercises you will learn how to use statistical tools in
   Excel.
   Delete everything in the spreadsheet and then fill columns A and B with the
   following numbers:
   Column A: 16, 14, 14, 13, 20, 21, 23, 23, 26, 11, 14, 27, 10, 15, 23, 12, 16, 18,
   28, 17, 18, 25, 24, 24, 30, 19, 14, 16, 26, 18
   Column B: 37, 38, 38, 37, 40, 41, 40, 40, 43, 36, 36, 42, 37, 39, 39, 36, 39, 38,
   43, 37, 40, 40, 40, 42, 42, 40, 36, 39, 43, 39

   Note: use MS Word formatting to enter these numbers using copy and paste commands
   (see above).

   Select: Tools/Data Analysis/Descriptive Statistics. Select input range A1:B30,
   check confidence intervals box, check the descriptive statistics box (at the
   bottom), select output range C1:E10, and press OK. What is Standard Error,
   Median, Mode, Standard Deviation, Variance, Kurtosis, Skewness, Confidence
   interval?

   18. Delete columns C-F: selecting these columns and then go to the menu
   Edit/Delete. This is a good way to delete entire columns or rows. To make a
   histogram, you need to create a column with value thresholds (called bins in
   Excel). Excel can create bins automatically, but you have better control of the
   results if you make bins yourself. Put values 10 and 15 in cells C1 and C2,
   select both cells and autofill the column down till the cell C7. Go to Tools/Data
   Analysis/Histogram; input range A1:A30; bin range C1:C7; specify the output range
   E1:F8.

   Bins are threshold values that separate value classes. For graphs is better to
   specify middle values for each class. Thus, edit the first column in the output
   by putting the number 8 instead of 10, 13 instead of 15. Then autofill this
   arithmetic series. Select the output block of cells (don't include the last
   line), and plot the histogram.

   19. Estimate correlation matrix (Tools/Data Analysis/Correlation). Then make a
   single-factor ANOVA. What is SS (sum of squares) within groups, between groups
   and total? Why there is 1 degree of freedom (df) between groups and 58 df within
   groups? What is mean square error (MS)? What is F and Fcrit? What is P-value? Is
   the difference in means statistically significant? If the difference was
   non-significant, would it mean that mean values are equal? Make a 2-factor ANOVA
   without replication. What does it mean? When we apply this kind of analysis?

   19. Make regression analysis. Check boxes: confidence level, residuals,
   standardized residuals, residual plots, line fit plots, normal probability plots.
   What is R-square, MS, F, P? Explore all output tables and graphs. Explain why we
   may need all this information. What are standard residuals? How to find outlier
   points in regression analysis.

   Notes:
     * You can do regression with multiple variables. For example, you may have 1
       column of Y-values and 3 columns of X-values. But it is important that all
       X-value columns are adjacent to each other.
     * Excel does not do advanced statistics, e.g. >2-factor ANOVA, ANCOVA, GLM. To
       do these analyses use either SAS or MINITAB for Windows.

   [1][back.gif] [2][up.gif] [3][forward.gif]
     ____________________________________________________________________________

   [4]Alexei Sharov 12/4/98

References

   1. http://alexei.nfshost.com/PopEcol/popecol.html
   2. http://alexei.nfshost.com/PopEcol/popecol.html
   3. http://alexei.nfshost.com/PopEcol/lab2/lab2.html
   4. http://alexei.nfshost.com/~sharov/alexei.html