FRWS 640010/23/2018

Lab1 – Introduction to Excel

Objective: The objective of this lab is to familiarize the student with some of Excel’s more commonly used features and capabilities. After completing this lab, students should be able to fill and format cells, enter equations and IF statements, understand fixed and relative cell references, use a lookup function, create graphs, and print a desired area in a desired format. In addition, this lab should familiarize students with a variety of common functions.

Entering data – formatting cells, filling cells

Get the following data off the server:

  1. Format the data to 2 decimal places.
  2. Add a column to the left of the data labeled “YearFill”. Use the fill function to enter years 1989-2003.
  3. Format column heading cells using wrap text.

Entering equations/formulae –relative versus fixed references, cell names, inserting versus typing a function, Excel help

  1. Add a column to the left of the “Year Fill” labeled “Year Equation”. Enter 1989 in the first cell and then use a formula to fill in the remainder of the values (use = and mouse rather than entering manually).
  2. Calculate the geometric mean (geomean), maximum (max), minimum (min), and median (med) values of lambda using Excel functions (Excel function names in parentheses).
  3. Use Excel Help to get an explanation of geomean.
  4. Add 3 columns titled “Diff from Mean”, “Diff from Min”, and “Diff from Median”. Copy calculated values (2 methods) to a cell above the respective column.
  5. Use a formula to calculate these values.
  6. Name the cell values (geomean, min, and median) and repeat exercise.

If statements

Get the following data file off the server:

Acorn woodpecker demographic data
From: Stacey, P. B., and M. Taper. 1992. Environmental variation and the persistence of small populations. Ecological Applications 2:18-29.
  1. Let the carrying capacity of the population = 52. Add a column to the right of the “No Constraints N(t)” column titled “Model Constrained N(t)” or something similar. To fill this column, constrain the population maximum to be =< the carrying capacity using an if statement.
  2. Turn auto-calculation off. Change carrying capacity to 49. Hit F9 to re-calculate manually. Turn auto-calculation back on.

Lookup function

  1. Add a column to the right of the “Model Constrained N(t)” column titled “Empirical ”. Use the lookup function to pull the value from the Census Data.
  2. Change the cell from a lookup formula to a value.

Graph

  1. Graph the empirical and model estimated  versus year using appropriate labels and graphics.
  2. Create a second graph of the empirical and model estimated  versus year with regression lines.
  3. Calculate the 95%CI for the following data and then graph the means and their 95%CI (output from SAS).

Print

Print out each worksheet of your workbook formatted to fit on 1 (either portrait or landscape, whichever looks best) page with an appropriate header. Try both setting the page format and setting the print range.

Extra Credit – Array Function

Pick up the spreadsheet FRWS6400_Lab1_STD&SE_Data.xls off the server. Use the FREQUENCY function to make a histogram of the means for samples 1-100.

1