Part 3—Import and Process Sea Ice Data

Step 1 –
Import your Data into an Excel Spreadsheet

At this point in the project Dr. Meier turns the instruction over to Thomas and Susan. They work with the students to bring their sea ice measurements into Excel and examine the trends in the data.

Open your NovemberResults.xls file in Excel by double-clicking it or by choosing File > Open.... Your worksheet will show 3 columns: Column A (unlabeled) shows the measurement number, Column B shows the area of ice (in pixels), and Column C shows the slice number from which the measurements were made.

If you saved your measurement Results as a text file (.txt extension) you will need to import them into Microsoft Excel.

In Excel, select File > Open.... In the Open dialog box, select the option to Enable All Readable Documents. Browse to where you stored your file and open NovemberResults.txt. Use the Text Import Wizard to format the data, and save the reformatted file as a Microsoft Excel file with the file name NovemberResults.xls.

  1. Open Excel, and use File > Open... to open the NovemberResults.txt file. This will automatically open the Text Import Wizard, which formats the text data for use in the Excel spreadsheet. The data is separated (or delimited) by tabs, so click the Delimited option.
    text import wizard 1
  2. Leave the Delimiters option set to the default setting, Tab. Click Next.
    text import wizard 2

  3. Keep the default setting for Column data format and click Finish.
    text import wizard 3

  4. Save the file that opens as a Microsoft Excel Workbook file type, so that the file name changes to NovemberResults.xls.
    November results xls Screen Shot

Step 2 –
Process your Data

  1. Check over the data to be sure you have all 28 rows.
  2. Delete any rows that have duplicate slice numbers in column C. Select an extra row by clicking its row number and choosing Edit > Delete. Also, check that your slice column contains all numbers from 1 to 28. If it doesn't, figure out which slice you missed and go back to ImageJ to measure it. If you're missing more than one year, it may actually be easiest to repeat all measurements and re-import the file. Finally, make sure the slice numbers are in order. If they are not, select all three columns and go to Data > Sort. Select the slice column and Ascending to have Excel reorder them.
  3. Add the word "Year" as a header label in the first column. You'll replace the numbers in this column from ImageJ with the year they represent: in cell A2 type 1978. In cell A3, type =A2+1 then copy the formula down through the rest of the column.
  4. Now that you have the years, you will not need the slice column (Column C) so you can delete it. Select the entire column and choose Edit > Delete.
  5. Add a new column between Columns A and B. Enter a label in the top cell of the new column (cell B1). Type "Area (millions of square kilometers)."
  6. formula dialogue in excel
  7. Enter this formula in cell B2: =(C2*625)/1000000

    This will multiply the area in C2 (the number of pixels covered by ice) by the area of a pixel (625 km2) and divide by 1 million. The result will be the total ice extent (area) in millions of km2.

    • Copy this formula down Column B.
    • Format the cells as numbers, and limit them to two decimal places.
    • Save your edited Excel file.

Step 3 –
Show the Trend in the Data

What do the data say? Is the sea ice extent really getting smaller in November of each year? Is it true that polar bears are having to wait longer for the ice to return to Hudson Bay? Graph your data to document your answer to this important question.
  1. Select the Year and Area (millions of km2) columns (columns A and B) then click the Chart Wizard icon or choose Insert > Chart... from Excel's menu bar.
  2. Make an XY (Scatter) graph with points and smooth lines (called a Smooth Marked Scatter). Place Time (in years) on the X axis and Sea Ice Extent (in millions of square kilometers) on the Y axis.
    1. Highlight the cells you are going to graph: Year and Area.
    2. Click on the Chart Wizard button.
    3. In the Chart Wizard dialog box, choose XY (Scatter).
      chart_wizard in Excel
    4. Choose Next, Next, and Finish.
    5. Your graph should look something like this Sea Ice Extent graph in Microsoft Excel.

      area of sea ice graph in excel

  3. Add a linear trend line to your graph. Click once on the line of graphed sea ice area data to select it, then choose Chart > Add Trendline.... In the Format Trendline dialog box, choose Type and select the Linear line option. Click OK.
    • What does the general slope of your trend line say about sea ice in Hudson Bay since 1979? Describe the relationship shown by your trend line. At the rate of decline shown by your chart, how much ice would you predict will be in Hudson Bay in November, 50 years from now?
  4. With your chart active, re-open the Format Trendline window, click on Options. Use the Forecast tools, to project your trend line into the future. Set the Forecast field to go Forward by 50 Periods (years).
    Note to Numbers software (Mac) users: you can use the Forecast function to predict the Sea Ice Extent in the year 2050.
  5. trendline_options_forecast in Excel Format Trendline dialog window in Microsoft Excel.

  6. Experiment with the trend line options. Predict the year that there will be no sea ice at all (0 square kilometers) in November.
sleds parked on shore for the summer
In another village, sleds are waiting on shore for the sea ice to return. August 2007. Photo Source: Betsy Youngman.

The trend that the students find is astonishing. They decide that they will meet with the elders and discuss their findings. They prepare presentations and call a meeting for the next afternoon.

Step 4 –
Save your Excel Spreadsheet and Graph.

  1. Select and copy your graph. Create a new word processing document and paste the graph into it so you can compare it with the graph you make in the next section.
  2. Save your worksheet and graph in the Excel file by choosing File > Save As... and giving it an appropriate name. Save your file in the folder with the other work for this project.