Covariance - Excel

In order to do this section, download this Excel file called Temperature.

To download Excel files, you must configure your browser (Netscape) to recognize these files. If your browser has not been set up for Excel files yet, click here to learn how.

Recall in the introduction of this lesson, the question of how temperatures changes with latitude and longitude. The Excel file you downloaded contains the temperatures, latitudes and longitudes for thirty U.S. cities. The data gives the normal average January minimum temperature in degrees Fahrenheit with the city's latitude and longitude.

We will use this data to help us explore how temperature changes from north to south and from east to west..

1. Intuitively, we would expect that the further south the city is, the temperature will be greater. Look at the graph in the spreadsheet to answer the following questions.

1. Does the graph support this intuition? Explain.

2. What would the sign of the slope of the best-fitting line be?

3. Which cities would you consider outliers?

1. Find the sum and mean of the temperature data (COLUMN A).

2. List at least two cities who have "average" temperatures.

1. Find the sum and mean of the latitude data (COLUMN B).

2. List at least two cities that are close to the "average" latitude.

3. List three cities whose temperature and latitude are close to the mean data point. Recall, that this is the point formed from the x (or temperature) mean and the y (or latitude) mean.

2. Calculate the deviations for the temperature (COLUMN D) and latitude (COLUMN E) data.

1. Without doing any calculations, what is the mean of both deviation lists?

3. Fill in the blanks to the following sentences with either greater than or less than.

1. Miami, Florida's temperature is ______ than average and its latitude is ______ than average. Therefore, the sign of Miami's contribution to the covariance is _________.

2. Seattle, Washington's temperature is ______ than average and its latitude is ______ than average. Therefore, the sign of Seattle's contribution to the covariance is _________.

3. St. Louis, Missouri's temperature is ______ than average and its latitude is ______ than average. Therefore, the sign of St. Louis' contribution to the covariance is _________.

4. List the cities from smallest to largest, in terms of its relative influence on the covariance.

1. Calculate the product of deviations (COLUMN F).

2. What type of cities have the most influence on the covariance?

3. Calculate the covariance. (The mean of the product of deviations.)

4. Recall, that the sign of the covariance and the sign of the slope of the best-fitting line are the same.

1. Look at your answer to part 1b.) , does the sign of the covariance you calculated support your answer?

The following questions relate to the temperature and longitude data located to the right of the latitude data in the spreadsheet.

5. Look at the graph of temperature versus longitude.

1. Do you see a relationship between the two? If so, what is it?

2. Would the sign of the slope of the best-fitting line be positive, negative or close to 0?

6. For the temperature versus longitude data,

1. Find the sum and mean for the temperature and longitude data. (COLUMNS H and I)

2. Calculate the temperature and longitude deviations. (COLUMNS J and K)

3. Calculate the product of deviations. (COLUMN L)

4. Find the covariance. (CELL L35)

7. Use question 5 as a guide to explain the connection between temperature, longitude and covariance for the following cities.

1. Portland, ME

2. Denver, CO

3. Cincinnati, OH

8. Does your answer for the covariance support your answer to 8a) and 8b).

9. Compare the two covariances you calculated for the longitude and the latitude.

1. If you had to predict the temperatures for cities not listed in the spreadsheet, would you use the city's latitude or longitude to get a more accurate prediction? Explain.