Assigned: 10/22/03
Due: 10/29/03 at the start of class (time will only be available in class on 10/22)
Remember: late penalties are heavy
Assignment:
Imagine that you have been asked to create the spreadsheet shown on the attached page. The task involves calculating the richness of a habitat using some established methods. No prior knowledge is needed, all information about the task is provided here.
Details:
1) Headings need to be typed in as shown.
2) The number of animals of each of the species found in Zones 1-6 need to be typed in as numbers.
3) The total number of animals of each species across all zones must be calculated using formulas.
4) Total number of animals found in each zone must be calculated.
5) The square root (sqrt) and natural log (ln) of the number of animals found in each zone must be calculated using formulas. Look for Excel functions to handle these.
6) The number of species found must be calculated using formulas. Look for an Excel function to handle this.
7) Menhinick’s richness index and Margalef’s richness index must be calculated using formulas. Mathematical formulas are given below[1] (you must determine the Excel formulas needed).
8) The summary results (“Total Found” down through “Margalef’s richness index”) on the last column (“Total”) should be calculated down, not across. I.e., don’t sum the square roots, take a square root of the total across all species in all zones.
9) The spreadsheet should be formatted as shown, including title, headings, totals, etc. Auto Format using “Classic 1” style will do most of the work for you. The title should be centered across the first six columns (using Merge and Center if auto format doesn’t do it), and be Bold. Several headings should be bold – Species, Total, and row headings from “Total Found” down. Other headings are regular text. Border lines should be included as shown (auto format will do some, format painter or format menu can be used for others).
10) Formatting should be used to control the number of digits after the decimal point for squareroot, natural log, and richness indices values.
11) Max, Median, Mean, and Min must be calculated using formulas. Format as shown.
12) Add headers and footers as shown – with file name and sheet in header and date and time, and page numbers in footer. Make sure you use fields, not typing the exact values.
13) Type your actual name where the example says “Your Name Here”.
14) Make sure that the whole spreadsheet prints on one page. Adjust under Page Setup if necessary
Questions:
1) If Zone 2 data were adjusted to include 10 previously overlooked members of species G, what all would change in the spreadsheet? (Specifically, which cells would change? How?)
Menhinick’s = Number of Species Found
Squareroot of # Animals
Margalef’s = (Number of Species Found - 1)
Natural Log of # Animals
Turn In:
1) Print Out of Spreadsheet
2) Disk
3) Answer to Question below