Data Analysis using Microsoft Excel
Using Microsoft Excel, enter the Age and Income data (textbook exhibit 7.17) into a spreadsheet.
•From the material in slides 22-27 this week on the intuitive meaning of statistical correlation (especially slide 25), calculate the correlation between Age and Income in a similar way. You should insert columns to show intermediate steps in calculating z-scores.
Note that the intent is that slide 25, (i.e.the single formula that summarizes correlation as the “average of the cross product of z-scores” is much easier to understand/memorize what correlation is really all about than a several page equation (as in the text). Doing this exercise allows you to try this out for yourselves…. and when you you confirm that your calculations match the next step below…..; you get to experience that “magical moment of insight!”.
•Verify that this is equivalent to the correlation output in Excel’s Data Analysis routines (i.e. CORREL( ), is an Excel function to check and compare your calculation with Excel’s.