CLIMATIC CHANGE: USING A SPREADSHEET
-Using MS Works-
An interesting aspect of the local community is its climate change over the last half-century: Which year was the coldest? Which year was the warmest? Is it generally warmer or colder now? This can be accomplished using a local data and a spreadsheet.

PART A: Planning the Spreadsheet

File  Edit  Print  Select  Format  Options  View  Windows  Help 
Formulas                 
      FILE  NAME         
  A  B  C  D  E  F  G  H 
1  Student               
2  Year  Average Temp.             
3  1947               
4                 
5                 
50  1997               

Press ALT to choose commands, or F2

    Using an appropriate source, record the average yearly temperature for your community for as far back as possible, perhaps 50 years (see Detroit data below).
  1. On note paper, organize and complete a spreadsheet similar to the above.

PART B: Computerizing The Data

Computerize the data by following these steps:
  1. Access the computer by:

    2. a) LOGIN- (Assigned by Teacher)
    b) Password- (Assigned by Teacher)
  2. Access MS Works
  3. Select 'Create New File' and then 'New Spreadsheet'.
  4. Begin entering the average annual temperatures by:

    5. a) Moving the cell cursor to cell A1
    b) Selecting the 'column width' command from the format menu to display the Width dialogue box.
    c) Entering '10'. The student's name column is now 10 characters wide: enough for initial and full last name!
    d) Moving the cell cursor to cell B2
    e) Selecting the 'column width' command from the format menu.
    f) Entering '14'. The Average Temperature column is now 14 characters wide.
  5. With the cell cursor begin labelling by moving it to:

    6. a) cell A1 and entering a name. (eg. group name; individual name)
    b) cell A2 and entering the label 'Year'
    c) cell A3 through A52 and entering the actual dates. eg. 1967.
    d) cell B2 and entering the label 'Average Temp.'
  6. Enter the average annual temperatures by moving the cell cursor to:

    7. a) cell B3 and entering the average temperature for the 1st year.
    b) cells B4 through B52 until all data entered beside corresponding years.

PART C: Performing Calculations

We can calculate averages for all and parts of the time period, as well as isolate maximums and minimums.
  1. Save the data by:

    2. a) selecting the 'SAVE' command from the 'FILE' menu, and
    b) typing file name 'CLIMCH' (Press ENTER)
  2. Setup spaces for formulas and labels by: ( NB. Dates can vary!)

    3. a) moving cell cursor to cell A54 and typing 'Overall Av'
    b) moving cell cursor to cell A55 and typing 'Av: 41-66'
    c) moving cell cursor to cell A56 and typing 'Av: 67-91'
    d) moving cell cursor to cell A57 and typing 'Max: 41-66'
    e) moving cell cursor to cell A58 and typing 'Min: 41-66'
    f) moving cell cursor to cell A59 and typing 'Max: 67-91'
    g) moving cell cursor to cell A60 and typing 'Min: 67-91'
  3. Begin calculations by:

    4. a) moving the cell cursor to cell B54 and entering the function-
    =AVG(B3:B52)


    b) moving the cell cursor to cell B55 and entering the function-

    =AVG(B3:B27)


    c) moving the cell cursor to cell B56 and entering the function-

    =AVG(B28:B52)


    d) moving the cell cursor to cell B57 and entering the function-

    =MAX(B3:B27)


    e) moving the cell cursor to cell B58 and entering the function-

    =MIN(B3:B27)


    f) moving the cell cursor to cell B59 and entering the function-

    =MAX(B28:B52)


    g) moving the cell cursor to cell B54 and entering the function-

    =MIN(B28:B52)


    NOTE: These formulas will also appear on the formula line.
    h) Save and close.

PART D: Producing a Multiple Line Graph

The object of this part of the exercise is to visually compare the patterns from. eg 1941 to 1966, and 1967 to 1991. ( Note: These instructions may need small alterations with newer versions of MS Works)
  1. Open the 'CLIMCH' spreadsheet.
  2. Select the 'New Chart' command from the 'View' menu.
  3. Accept the series not selected message (Press ENTER)

    4. NOTE: Status line should read 'chart'.
  4. Move the cursor to A3, then highlight the cells from A3 to A27.
  5. Select the X-series command from the 'Data' menu (Press ALT-D-X), making the highlighted years the X-Series for the chart.

    6. NOTE: The graph will contain two lines but the X axis calibration will only apply to one: 1st 25 years.
  6. Move the cell cursor to B3, then highlight the cells from B3 to B27
  7. Select the 1st Y-Series command from the Data menu. (Press ALT-D-1), making the highlighted data for the 1st 25 years the first Y-Series for the chart.
  8. Select the line command from the 'Format' menu to create a line graph.
  9. Select Chart 1 from the 'View' menu. The screen clears and the graph is displayed.
  10. Press'Escape' to return to CHART screen.
  11. Do the same thing for 2nd set of 25 years: cell cursor to B28, highlight to B52(F8), select 2nd Y-Series, select 'line', then select Chart 1 for viewing.

PART E: Legends

  1. Select the 'Legends' command from the DATA menu.
  2. Highlight '1st Y' in the series list.
  3. Move cursor to 'Legend' option for Chart 1, type 'FIRST 25 YRS', then press ENTER.
  4. Highlight '2nd Y' in the series list, move cursor to the Legend option and type 'SECOND 25 YRS' (Press ENTER)
  5. Press 'Escape' to return to the CHART screen.
  6. Select Chart 1 from the 'View' menu to view the graph with its legends. (Pick line chart)
  7. Save the chart by selecting the 'SAVE' command from the 'File' menu.

PART F: Titles

  1. Select 'Titles' command from the 'Data' menu.
  2. Type AVERAGE TEMPERATURE CHANGE

    3. Subtitle- (Community) from (Range of Dates):by (Your Initials) .
  3. Move the cursor to the Y-Axis Option and type AVERAGE ANNUAL TEMPERATURES (unit)
  4. Move the cursor to the X-Axis Option and type 1st AND 2nd 25 Years .
  5. Exit the dialogue box.
  6. Select Chart 1 from the 'View' menu to display the graph.
  7. Select the ;SAVE' command from the 'File' menu.
  8. Select the 'Print' command from the print menu to print the graph.
SAMPLE DATA: DETROIT METRO AIRPORT TEMPERATURES (F) 
Date  Jan  Feb  Mar  Apr  May  June  Jul  Aug  Sept  Oct  Nov  Dec  AV. 
1961  22  31  39  43  55  67  72  71  68  55  40  29  49.1 
1962  22  22  33  46  63  69  69  69  61  54  39  24  47.5 
1963  16  18  37  47  56  68  72  67  60  58  43  21  46.8 
1964  28  26  34  48  61  66  73  65  61  47  41  28  48.1 
1965  25  26  30  45  63  67  69  68  63  48  41  35  48.3 
1966  20  28  37  44  52  68  74  69  62  51  42  29  48.0 
1967  29  24  36  48  52  70  69  67  60  51  35  31  47.7 
1968  21  24  38  51  56  68  72  73  66  54  41  28  49.3 
1969  23  28  34  49  57  65  73  73  65  51  38  26  48.6 
1970  17  24  33  49  61  68  73  72  65  54  40  29  48.8 
1971  21  27  32  45  56  71  70  70  67  59  39  33  49.1 
1972  24  25  33  45  60  64  71  69  63  47  37  30  47.3 
1973  29  25  43  49  56  70  73  73  65  56  41  29  50.7 
1974  27  24  36  49  55  66  73  72  60  49  41  29  48.2 
1975  28  28  33  41  63  69  72  72  59  53  47  29  49.5 
1976  19  33  40  50  56  71  73  70  62  47  34  22  48.1 
1977  13  25  42  52  65  66  76  71  65  48  41  26  48.9 
1978  20  16  30  46  59  67  71  72  68  50  41  29  47.3 
1979  19  17  38  45  57  67  70  68  63  50  40  32  46.9 
1980  25  22  31  46  60  64  73  73  64  46  37  26  47.2 
1981  19  29  37  50  56  68  72  70  61  48  41  28  48.2 
1982  17  21  33  43  64  65  72  68  62  53  42  37  48.0 
1983  29  32  39  44  54  68  75  74  64  52  41  21  49.3 
1984  18  33  29  48  55  71  71  73  61  55  39  34  48.8 
1985  20  24  39  51  60  63  71  70  64  53  43  22  48.2 
1986  24  25  38  51  61  67  75  69  66  53  37  32  49.7 
1987  26  30  40  51  63  71  76  72  65  47  44  34  51.4 
1988  24  23  37  49  62  70  77  75  63  46  42  29  49.8 
1989  33  24  35  45  58  68  73  70  62  52  38  18  47.9 
1990  34  31  40  49  57  69  72  71  65  53  44  33  51.3 
Homepage / Environment & Resource Management / Order Form / Lesson Plan