FOREST WATCH Protocol Compilation Spreadsheets ver 1997-2002/2.1 & ver 5.0-95/2.0 Pete Favuzza, Jan 2002 Instructions This is an expanded set of instructions that go beyond the cursory information provided on the ReadMe sheet of the spreadsheet workbook. It is a page by page, step by step walk-through of the program and the individual spreadsheets. 1. Installation 2. Terms 3. Version 2.1 changes 4. Differences between Excel97-2000 and Excel5.0/95 versions 5. Cover sheet 6. ReadMe 7. Source Info 8. Tree Form 9. Site Assessment 10. Tree Height Measurements 11. DBH 12. Needle Retention & Fascicle Length 13. Water Content 14. Manually Compiled Data 15. Needle Length & Injury 16. Master Data 17. Credits & Contacts 1. INSTALLATION Depending on your operating system there will be several ways in which it can be installed on your hard drive. In essence, you should create a separate directory such as "ForestWatch" and copy the files from the floppy to this directory. It is also suggested that you make a "working" copy of the file in order to maintain the integrity of the master. Name this second copy "FWWork" or some other name which will make its purpose obvious to the user. 2. TERMS The term WORKBOOK refers to a SET of related SPREADSHEETS combined into a single file. Topics 5 through 16 refer to specific spreadsheets within the Forest Watch workbook file. The term PAGE will not be used here to avoid confusion since that term is used to refer to the length of a spreadsheet for printing purposes. 3. VERSION 2.0 & 2.1 CHANGES A field for site elevation has been added to the Source Information sheet and a minor change was made on the Needle Length & Injury sheet to display a number of related calculations. Fields for the inclusion of this new data were added to the Master Data Sheet. The most obvious change in this version is the addition of another site assessment protocol entitled Tree Form. Its purpose is to provide for a qualitative site assessment and asks for written descriptions of your site's characteristics. V2.1 addresses calculation and format errors found on the Master Data Sheet and includes code added by Ryan Huntley for data transfer purposes at UNH. 4. DIFFERENCES BETWEEN Excel97-2000 and Excel5.0/95 VERSIONS. This spreadsheet was designed using Excel2000, the most recent version of the program in use at the time. The Excel97-2000 version includes Data Validations which make it virtually impossible to enter invalid data, whether due to a typo or by exceeding logical parameters. Using the TAB key will also allow movement from one cohesive data field to the next. Neither of these is available in the 5.0/95 version. The program was converted to this format for those using older versions Office/Excel or MacIntosh computers for which the most current version of Microsoft Office was 5.0/95. 5. COVER SHEET Please be aware that this sheet and all others may fit your screen differently than originally intended depending on screen size and display parameters. What might seem to be random or non-uniform spacing is a direct result of any such differences between your computer and mine. While they might not be visually appealing they do not affect the integrity of the program. The cover sheet contains the official logos of Forest Watch, National Science Foundation, and the New Hampshire Space Grant Consortium under whose auspices this program was written. The version number signifies which version of Excel this file is meant to be used with, as well as its own version number. Therefore v97/2.0 is Excel97 version 2.0. 6. README This section contains brief program information and instructions for its use. Those instructions are augmented by this document. This program may be used in a variety of ways: It can be used simply as a place to record data and calculations performed by hand. In this case, all pertinent data should be entered directly on the Manually Compiled Data sheet from where it will be automatically transferred to the Master Data sheet... ...OR it can also be used to enter raw data and let the program perform and record the calculations. The results of these calculations are then transferred to the Master Data sheet as described above. Another option is to do BOTH of the above, allowing the program to confirm the work of your students. However, in the case where both Manually Compiled Data and program-calculated data appear, it is the program-generated data, which is transferred to the final Master Data sheet for submission. In addition, certain protocol data sheets allow for various methods of recording data and determining results. Explanations on how to do this and which results are accepted by default appear in the appropriate sections, which follow below. This section also contains instructions for submission of your data either by mailing hardcopy (printout), mailing on floppy disk, or by email. In the Excel97 version, a filename is suggested based on information submitted on the Source Info page. In either version, this name should consist of the first six (6) letters of your school's name followed by the year of the needles' growth. For example, the students of Northwestern Regional #7 are sending samples of needles that flushed and grew during the summer of '01. The file would be named northw01.xls, EVEN IF these needles weren't collected and studied until the spring of '02! Finally, updated contact information concerning the author, the Forest Watch program director, and the Forest Watch Internet website are found on this sheet. Please report any bugs, errors, or problems with the program and feel free to make suggestions concerning its use, additions or improvements. 7. SOURCE INFO Please fill in the fields on this page carefully. This information will be transferred to the Master Data sheet as well as to other locations in the workbook where it is needed. This saves you the time and work of having to type in redundant information such as Tree Tag Numbers on each of the protocol sheets. A field labeled "Needle Year" has been added to indicate the flush or growth year of the needles being sampled. This is the year that should be indicated in the name of the file when it is prepared for submission. This was done to avoid confusion in the event that the Collection Date didn't occur until the spring of the following year. The Site Elevation field has been added in order to provide another piece of helpful data when known. 8. TREE FORM This is a completely new protocol that has been added to the Forest Watch "suite". Its purpose is to provide a narrative and pictorial (when possible) profile of your study site. Space has been provided for text input. Please use this space efficiently, being as concise yet as detailed as possible. 9. SITE ASSESSMENT This is the sheet on which you will record data concerning Canopy Closure and Ground Cover. Up to 30 observations of Closure and Cover may be recorded for each diagonal. Where there was evidence of vegetation it should be recorded by entering a "+" sign. Where there was no vegetation visible, enter a "-". Percentages are automatically calculated based on the number of observations performed and recorded. The split frame you will notice on the left side of the screen was provided to allow the labels to remain visible just in case data recording proceeded beyond the width of the visible screen. 10. TREE HEIGHT MEASUREMENTS There are five sets of the following measurements, one for each tree in your plot. The Tree Tag numbers will automatically appear on each set based on the information you supplied on the Source Info sheet. The baseline distance, that is, the distance from the tree to the observer, should be at least 10 meters. Distances less than 10 meters tend to yield inaccurate results and will not be accepted by the program. If your distances are less than 10m then either new measurements will need to be taken, or the calculations may be made manually and entered on the Manually Compiled Data sheet. After entering the Baseline Distance and Clinometer Angle the program will then record the proper tangent. After entering the Eye Height of the observer the program will then calculate the height of the tree. As data is entered for each trial the Average Tree Height is recalculated and recorded. For establishing the Live Crown Height the same method may be used. In addition, it is possible to record a height achieved by direct measurement, such as use of a tape measure. If both methods are used and recorded, the calculated method is accepted as the default. This is done to maintain consistency in measurement methods. Therefore, record only the Direct Measurement values if you wish this method to be accepted. This also applies for the Sample Height Measurements. 11. DBH Diameter at Breast Height measurements can be taken and recorded using either of two methods. Actual circumference measurements may be taken, entered and calculated yielding average circumferences from which the diameters will be calculated by the program. Another option is to use a Forester's measuring tape, which is designed to indicate a tree's diameter when wrapped around its circumference. If both the Circumference data and Direct Measurement data are recorded, the program defaults to the Direct Measurement for Accepted Results. 12. NEEDLE RETENTION & FASCICLE LENGTH Enter the data for Needle retention and then record the lengths of ten fascicles for both the north and south quadrants of each of your five trees. Averages will be automatically calculated and recorded. 13. WATER CONTENT OF FRESH NEEDLES Record the mass of the Fresh Needles, Bag With Fresh Needles, and Oven-dried Bag with Needles for both the North and South Quadrants of each tree. The program will calculate the mass and percentage of the water in the needles when they were fresh. 14. MANUALLY COMPILED DATA This page may appear to be out of place, but is located here for a logical reason. For the teacher planning to have the students do ALL of the calculations by hand and with no interest in using the program even as a validation exercise, the sheets following the Source Info sheet can be skipped entirely. As a result, the Manually Compiled Data sheet and the Needle Length & Injury Data sheets that follow are the only ones necessary to complete. Data entered on this sheet will be transferred automatically to the Master Data sheet as long as the program has not been used to make any of the same calculations. In the latter case, as mentioned in the ReadMe section, program calculated results are accepted by default. A section for manual calculations of Needle Length & Injury is NOT included on this sheet because of the sheer amount of data involved in those calculations. Program calculated results from that protocol sheet are automatically transferred to the Master Data Sheet. 15. NEEDLE LENGTH & INJURY DATA There are five sets of these measurements, for both the north and south quadrants, for each tree in your plot. The lengths of 30 individual needles are recorded in millimeters, NOT centimeters as in the Fascicle Length protocol. The presence or absence of Tip Necrosis &/or Chlorotic Mottle are recorded with a + or - sign. In the next column the program will, based on your input, indicate whether both forms of damage, a sign of ozone damage, were present. In the next column you will record the total damage, from ALL sources. The program will then calculate the percent of the needle that is damaged. Calculation of averages for all data appear at the bottom of each column and is automatically transferred to the Master Data Sheet. 16. MASTER DATA This is a READ-ONLY summary sheet. The results of all calculations from all the protocols (except Tree Form and Diagnosis) are compiled on this sheet. The data appearing here is protected and can only be changed by going back and altering the data on a specific protocol sheet. This sheet, along with the five Needle Length & Injury sheets, and the Tree Form & Dianosis sheet, are the only ones you need to send to Forest Watch if you are submitting data by regular postal mail. If you are submitting your data by floppy disk or email, refer to the field labeled "Save this file as:". Save your file using the name in this field. It is in a format that will be easily recognizable by the folks at UNH and will create a uniform standard among schools, avoiding the possibility of redundant user-created filenames. 17. CREDITS & CONTACTS Program bugs, questions, typos etc: Pete Favuzza, author, pfavuzza@esslink.com Data/File submissions: Ryan Huntley, FW Coordinator, ryan.huntley@unh.edu Internet Web Site: www.forestwatch.sr.unh.edu