Excel

1. Today’s exercise in Excel will examine correlations and scatterplots. If you haven’t already downloaded it, go to moodle and download correlation.xls. In this exercise you will look at a set of transportation variables that may or may not be correlated.

2. Notice that the top row of the sheet is frozen, if you scroll down you will see an orange table with various cells to fill in. There is one set of data and a table with US data, and one set of data and a table for California. Below is the table for the US. Note: VMT stands for “Vehicle Miles Traveled”, which is the total amount of miles that all of the vehicles in the US drive in a given year.

3. You will need to first make an educated guess as to whether or not the two variables are correlated. For example, do you think that VMT is correlated with Gross Domestic product?

4. Before proceeding, fill out your guesses for each of the relationships. What is the expected direction of the relationship and what do you think is the strength of the relationship?

5. Now you will need to fill out several columns in the table they have red text. There should be four blank columns, two for the US and two for California. Use formulas that I have already taught you, they should be simple.

6. Now calculate the correlation coefficients. In Excel the formula is =CORREL(array1, array2), where array1 and array2 are the appropriate columns of data. Do not include the column headers in the formula, and make sure you get all years (1966-2009). For example, to calculate the first correlation, you would type into cell E50 the following: =CORREL(B3:B46,C3:C46)

7. To find the t-stat, you will need the following formula:

with (n-2) degrees f freedom

8. Is the correlation statistically significant at the 95% level? You might need to Google a t-table and look it up.

9. Repeat these steps for all eight correlations.

10. Now make scatterplots of the data. For the VMT and Population scatterplot, you would do the following steps. Left-click your mouse in an empty cell. Go to the menu bar click on INSERT then CHART then Scatter plot with only markers.

11. Now right click in the empty chart and choose select data.

12. Click ADD Then type VMT and population into the name box, and select the appropriate VMT cells for the Series X values and the appropriate Series Y values for the population data. It should look like this: Click OK and then OK again.

13. You should see the following chart.

14. It looks ugly so you should clean it up a bit. Double clicking on any of the chart elements (e.g., the x-axis) brings up a dialog box. You can change the font, spacing and other elements. You don’t have to copy my chart, just make it look nice.

15. You can also add a trendline. CRight click on one of the markers and select Add Trendline, make sure linear is selected. Then hit Close.

16. Next class we will learn how to calculate the trendline on our own. That is what regression analysis is about.