Center Home -> Content Areas Home -> Math Home -> Project Activities -> Excel Activities ->

Analyzing Temperature Trends for U.S. Cities



Activity Description Activity Guide


Part 1: Conjecturing Temperature Trends

  • Have students view a map of the United States that contains lines of latitude and longitude.  Discuss possible similarities and differences between the climates in different regions of the US.  Have students conjecture a relationship between a city’s location and it’s monthly temperatures. 

  • Choose several sets of three cities along similar longitudes with different latitudes. Also choose several sets of three cities along similar latitudes with different longitudes. What similarities and differences do you expect to find between the monthly average temperatures in a set of three cities? Assign each group of students to explore a set of three cities. Based on the location of each city, the groups should predict the cities’ annual average temperature. Suggestions for sets of cities include:

    cities with similar latitude

  • Los Angeles, CA, Dallas, TX, and Montgomery, AL
  • Pittsburgh, PA, Lincoln, NE, and Salt Lake City, UT
  • Richmond, VA, Dodge City, KS, and San Francisco, CA

    • cities with similar longitude

  • Madison, WI, Springfield, IL, and Jackson, MS
  • Pittsburgh, PA, Roanoke, VA, and Miami, FL
  • Helena, MT, Salt Lake City, UT, and Flagstaff, AZ

Part 2: Analyzing Temperature Data for Three Cities

  • Each group should copy and paste the data for each city of interest into a spreadsheet.  Place City 1 in row 2, City 2 in row 3, and City 3 in row 4.  After the data is in the spreadsheet, highlight cells A2, A3, and A4 and select the Text to Column option under the Data menu. Follow the directions in the dialog boxes to format the columns so that each monthly average is in a separate column.  Once all three sets of data are properly formatted in columns, select column B and choose Delete under the Edit menu (to delete the entries of 30 years).  Label row 1 with the months of the year, starting with January in cell B1.

  • Calculate the mean and median monthly temperature for each city as well as the range from the highest monthly average to the lowest average.  For each city, answer the following questions:

    • What month had the highest average temperature? lowest? Is this month the same for each city? Is this what you expected?
    • What is the yearly average temperature? Which measure of central tendency, mean or median, would be best to use for reporting the average?  Why?  What season of the year has temperatures most similar to the yearly average?

  • Create a line graph containing the monthly averages for all three cities.  Describe the similarities and differences between the graph for each city. 

To make a line graph:

  • Highlight the cells containing the labels (in this case the City names) and the data to be graphed.

  • Click on the ChartWizard then click in the spreadsheet at the point where you want your graph to appear.  The first pop-up window shows all the different types of graphs that Excel can create.  Choose the Line icon and click on Next.

  • In the next pop-up window, verify the cell range reference for the data you have selected. If this is correct, click Next.

  • In the final step, give the graph a title and click on Finish.

  • Change the scale on the y-axis to emphasize the difference between the low and high temperatures.  How could this change in scale affect the interpretation of the temperature trends for each city?

  • Recall that some groups used sets of cities with similar latitude while other groups explored sets of cities with similar longitude. 

    • Compare several graphs made with cities of similar latitude.  Do these graphs look similar? What is different about them?  Will all graphs of cities with similar latitude look similar?  Why or why not?
    • Do the same comparison of graphs with similar longitude. Will all graphs of cities with similar longitude look similar?  Why or why not?
    • Compare a graph for cities with similar latitude with a graph for cities with similar longitude.  What are the similarities and differences between these graphs?
    • Based on the above comparisons, do you think there is a relationship between a city’s latitudinal and longitudinal position and its monthly and annual average temperatures?  Explain and justify your conjecture.
    • What other geographical factors could affect a city's temperature?

Part 3: A Closer Look at Cities with Similar Longitude and Latitude

  • Compare the temperatures of the following three cities with similar longitudinal positions.

    • Helena, MT 46.60 N 112.00 W
    Salt Lake City, UT 40.78 N 111.97 W
    Flagstaff, AZ 35.10 N 111.70 W

  • Predict the average annual temperature for each of these cities.  Justify your prediction.
  • Below are the monthly average temperatures for these cities. If a group has not already explored these three cities, have each group enter the data into a spreadsheet, calculate the range, mean, and median, and create a line graph of the three data sets.

      •  
      Helena, MT
      Salt Lake
      City, UT
      Flagstaff, AZ
      Jan.
      19.6
      27.9
      28.7
      Feb.
      26.4
      34.1
      31.5
      March
      33.6
      41.8
      35.3
      April
      43.4
      49.7
      42.3
      May
      52.5
      58.8
      50.4
      June
      62.1
      69.1
      59.8
      July
      69.2
      77.9
      66.3
      Aug.
      67.4
      75.6
      64.1
      Sept.
      55.4
      65.2
      57.3
      Oct.
      45.1
      53.2
      47.2
      Nov.
      31.6
      40.8
      36.8
      Dec.
      21.2
      29.7
      29.6

  • Compare the range, mean, and median for these three cities.  How does the mean compare with your prediction?  Reconcile any differences. 

  • Is it necessarily true that cities closer to the equator (further south in the US) have higher temperatures than those further away (further north in the US)?  What factors could be affecting the temperature in these cities?

  • Compare the temperatures of the following three cities with similar latitudinal positions.

    • Richmond, VA 37.50 N 77.33 W
    Dodge City, KS 37.77 N 99.97 W
    San Francisco, CA 37.62 N 122.38 W

  • Predict the average annual temperature for each city. Justify your prediction.

  • Below are the monthly average temperatures for these cities.  If a group has not already explored these three cities, have each group enter the data into a spreadsheet, calculate the range, mean, and median, and create a line graph of the three data sets.

    •  
    Richmond,
    VA
    Dodge City,
    KS
    San Francisco,
    CA
    Jan.
    35.7
    29.8
    51.1
    Feb.
    38.7
    35
    52.6
    March
    48
    43.5
    52.8
    April
    57.3
    54.7
    54.8
    May
    66
    64.2
    57.1
    June
    73.9
    74.4
    60.7
    July
    78
    80.3
    63.1
    Aug.
    76.8
    78.2
    64
    Sept.
    70
    69
    63.6
    Oct.
    58.6
    57.5
    61
    Nov.
    49.6
    43.2
    55.5
    Dec.
    40.1
    32.5
    51.1

  • What is the annual average temperature for each city?  What range of temperatures can be expected in each city throughout the year?  Is knowing the average annual temperature for a city enough to know the climate throughout the year? Explain.

  • Since these cities have similar latitude positions, what else could be affecting the monthly temperatures?

  • Compare the month with the highest average temperature in each city.  Why do you think the temperature in San Francisco peaks in October?

  • Using one of the sets of three cities, create a bar graph of the data using the month names as labels. Compare the bar graph with the line graph. Which graph do you prefer? Why?

Extension:

  • Conjecture locations in the US and in the world that might have similar monthly temperatures.  Also, conjecture a location that has a temperature trend that would curve opposite of one of the cities you investigated?  Search the internet to find data and compare the temperatures.


Back to Project Activities | Back to Math Homepage

Send questions or comments here.
Last modified on July 27, 2001.