Part 4—Create a Graph of Plant Abundance over Time

Step 1 –
Import Data into Spreadsheet

Import your file into a Microsoft Excel spreadsheets.

  1. Launch Excel, by clicking its icon in the Dock (Mac) or choosing it from the Start menu (PC).
  2. Choose File > Open... and navigate to the location where you saved the AlaskaAlders.txt file. Select the file and click Open.
  3. The "AlaskaAlders.txt" file will open. The Text Import Wizard will automatically launch. Follow the prompts to format the data for use in an Excel spreadsheet. In this case, accept all the default prompts of Next and Finish.
  4. Save the reformatted file as a Microsoft Excel file with the file name AlaskaAlders.xls.

Step 2 –
Delete Extra Data and Format Spreadsheet

Since Analyzing Digital Images is used for analyzing many types of digital images, extra data is saved automatically and will display on your Excel spreadsheet. You only need to use certain data for this chapter, so delete the columns of data that you don't need. Alternately, you can choose to use Excel's Hide feature to temporarily hide the columns. Note: you may want to make a copy of your spreadsheet before you begin if you want to use it later for other purposes.

  1. Click the column header so the entire column is highlighted. Then, choose Edit > Delete.
    delete column in spreadsheet


  2. At a minimum, the following columns should be displayed:
    • Image Name
    • % Pixels Masked
    • Max Red
    • Max Green
    • Max Blue

    The max colors data is useful in checking which mask was applied to the map. If you added information yourself in the user data section of the measurements screen that identifies the name of the mask, you only need to keep that column of information.
  3. Shade every other set of measurements to make it easier to identify data coordinates.

Step 3 –
Calculate Total Plant Abundance

The data that has been collected allows you to calculate the abundance of a plant species within a selected area. Using these calculations to determine the Percent Plant Abundance will allow you to chart the advance or retreat of your species and ice over the time period you have selected. In this example, the data will help show and explain the distribution of Alders in Alaska over the last 21,000 years.

  1. Launch Excel, by clicking its icon in the Dock (Mac) or choosing it from the Start menu (PC), and open the AlaskaAlder.xls file (or whatever you have named your species file).
  2. First, add a color legend to your spreadsheet, for each of the color masks you created in Part 3. The coverage percentages (Min and Max) are the ones created by Pollen Viewer as seen in Part 1. For purposes here, you will also need an average—the number average of the Min and Max.
  3. Create a blank table on the top-right of your spreadsheet to create your calculations and record your values for Percent Plant Abundance. It should contain the following information and look similar to the table below:
    • Years Before Present - list each year that you collected color-masked data for
    • Column Header for Min Alders
    • Column Header for Ave Alders
    • Column Header for Max Alders
    • Column Header for Ice
    TableBlank

  4. Add each year's data for "Ice" in the appropriate column in the table. See the example below; click the image for a larger version.
    AddIceToTable

  5. Add calculations for the summed averages for each image using Excel's formula features. Choose View > Formula Bar to make sure you can see the formulas as you build them.
  6. Your final spreadsheet and should look similar to the one below.
    AlaskaAlderFinal


Step 4 –
Make a Graph of the Data

  1. Use the Excel Chart Wizard to create a chart that displays the percentage of plant abundance and ice for the time frame you have chosen.
  2. Format your chart now to provide detail. From the main Excel menu, select View > Toolbox > Formatting Palette. Click once in the main body of your chart to select it and expand the Formatting Palette to include all its formatting features.
  3. Under Chart Options > Titles, make the following selections from the drop-down menu: Select Chart Title and enter "Abundance of Alders and Ice in Alaska"; select Horizontal (Category) Axis and enter "Years Before Present"; and select Vertical (Value) Axis and enter "Percent Cover".
  4. If desired, change the color of the plotted lines by clicking on each line individually. In the Format Chart Area window that displays, choose Line and from the Color drop-down menu, select the desired color. In our example, the line depicting ice will be changed to blue, and the lines illustrating Alders will changed to green.
  5. Next, you may need to modify the appearance of your chart. Double click on one of the X-axis labels. The Format Axis screen displays. Select Scale and adjust the default settings to the following:
    ChartXaxis


  6. Your final chart should look similar to the one below. Analyze your chart and consider the following questions:
    • When did the dominant species of plants move into your area?
    • Are there peaks in the coverage of plants in your area?
    • Were the trees predominantly deciduous or coniferous at different periods of time?
    • When did one plant type replace another?
    ChartFinal