Procedures for Forest Data Analysis (Excel)

Procedures for forest data analysis – (Excel)

I. Opening and saving file to your account

1. Go to Mr. Miller’s webpage. Go to Field Study Folder. Find file labeled “Forest Excel Data Sheets”. Open file. Now, save file into your student account before progressing further. Do not change the title of the file.

2. There are four sheets in the same document. Make sure the bottom tab is open to “Group Data Sheet”. Enter the names of the people in your group next to “Names” in cell B1. Enter your group number into B2 and your period into B3.

II. Entering original data from your group

1. Enter the tree name by clicking on the first box in column A, then the little arrow on the right, and chose your tree from the drop down list. The box is shorter than the list so scroll down for more choices. DO NOT TYPE IN TREE NAME. This will cause problems later. Then enter circumference data for that tree from your data sheet onto the Excel file. In column E, enter 1 for a canopy tree and 0 for an understory tree. Do this for all of your trees. Make sure you only put numbers into the circumference column and the canopy/understory tree column because the file will not recognize letters and give you an error message. In other words, do not put cm after the number.

2. When all trees and circumferences are entered, highlight from A6 to E28, then go to the toolbar above and select “Data” then “Sort A-Z↓” (near middle). Hit “Enter”. Before going to next step, check that the trees are now in alphabetical order. If they are, continue. If they are not, notify your teacher.

3. Highlight from A6 to E28 again (if it is not). Go to theTOP toolbar again and select “Data” then “Subtotals” (in the drop down). A text box should appear. Make sure “m2”, “totals”, and “C=1; U=0” are checked. Hit “OK”.

4. The basal area totals for each tree will automatically be entered into the table below for “Total Basal Area in Sq. m”. The total number of trees is also automatically entered into “Total Frequency” along with the totals for canopy and understory. DO NOT ATTEMPT TO ENTER ANY DATA IN THE BOTTOM DATA TABLE. When it says #N/A this means that there were no trees of this species in your list.

5. Hit “Save” again before you do anything else. Now “Print”. It will only print the second chart. Go to printer and collect your chart. You will need this chart for part II.

III. Entering data from the entire class

1. Go to my webpage and open the link “Class Forest Data Google Document”. Enter your data in to the class document based on your group number using directions below. READ THEM FIRST BEFORE YOU PROCEED!

1. Enter your group data for each tree species. If a cell has #N/A leave it blank. You need to make sure that the data for basal area, total frequency, and canopy trees is incorporated into the Google Doc. Only enter # of canopy trees (do not enter understory trees as the program will do that for you). Make sure you only fill in YOUR group’s data in your column.

1. On your own excel sheet, DO NOT TYPE in the RED or YELLOW colored area. The Red column for basal area and the yellow column for number of trees should add up the class totals automatically.

1. Do not print anything here.

IV. Printing and coloring pie charts

1. Open the tab at the bottom labeled “Dominance vs. Frequency”.

2. The class totals for basal area and number of trees for each species were automatically entered from your “Class Totals” spreadsheet.

3. The two pie charts below the data are currently colored in. You will need to remove the color so that you can color the pie pieces with colored pencils. To do this, right click on a single piece in the top pie chart. Choose “Format Data Series”. In the block there are two columns. In the left column choose “Fill” and then in the right column, choose “Solid Fill”. The colors should go away and leave the pie pieces white. If not, click on the paint can and choose white. Hit “Close”. This should remove all colors from your pie. Repeat this step for the bottom pie chart.

4. Click anywhere in the data table at the top. Go to “Print Preview”. You should see the entire page with data table and two pie charts. If you do, hit “Print” and print the number needed for your group. If it only shows a pie chart, you forgot to click in the data table. Close the print preview and do over.

5. Since it is not a color printer, take your black and white copies and color code your species by using the graph on the monitor. You must return the colors to the graph on your monitor. You can try to undo edit by hitting “Ctrl-Z”. Do this twice to restore both graphs. If this does not work, keep hitting Ctrl-Z until it does. If this does not work, close the file and reopen the file. Since you saved it immediately after doing “Class Totals”, the pie charts are still colored in. If you did not save it, see teacher for help in restoring colors.

6. Do steps 3-5 to the pie charts in the forth page “Canopy/Understory Trees”. Get the pie charts to white, print, and return the pie charts to their original colors. Color these in as well.

7. Do not hit save again. When you saved it at the end of “Entering Data” you saved it for good.

Save both pie charts for your report. Do not lose them as you will have to do it all over again!!!