Part 3—Import and Process Sea Ice 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.

Show me a trouble-shooting note

Hide

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.

Show me the details

Hide

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.
   
   
2. Leave the Delimiters option set to the default setting, Tab. Click Next.
   


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


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

1. Check over the data to be sure you have all 28 rows.
Show me how to deal with any duplicate, missing, or unordered data
Hide
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.
2. 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.
3. 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.
4. 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)."

5. 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.

1. Select the Year and Area (millions of km²) 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.
Show me how to make a graph
Hide
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).
   
   
4. Choose Next, Next, and Finish.
5. Your graph should look something like this Sea Ice Extent graph in Microsoft 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.
Show me how to make the trend line show a forecast
Hide
Format Trendline dialog window in Microsoft Excel.


5. Experiment with the trend line options. Predict the year that there will be no sea ice at all (0 square kilometers) in November.

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.

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.