Part V Pearson's Coefficient Correlation from 
Statistics Video Lectures Using Excel
I. Darin wants to determine whether there is a mathematical relationship between Performance Rating and Sick Days taken by his employees. 
He will use Pearson's correlation coefficient to measure the strength of this relationship and a regression equation to mathematically define 
the relationship.
  A. Open Excel book entitled Pearson's Correlation Coefficient. Use Data set Employee Performance Analysis.
  B. Increase the column H width to 40 using Format, Column, and Width.
  C. We will begin by measuring the relationship between Sick Days and Performance Rating.
  1. Type Sick Days and Performance Rating into H1.
  2. Place the cursor into I1 and choose fx, Statistical, and CORREL and OK.
  3. Enter the range D2:D24 into Array 1 (Those using version 8 can use the red down arrow.)
  4 Enter the range E2:E24 into Array 2 and choose OK. The result, a correlation coefficient of -0.24634, indicates little correlation between Sick Days and Performance Rating.
  D. Calculate these additional correlation coefficients.
  1. Repeat steps 1-4 under part C for Age and Performance Rating. Your answer of 0.377038 indicates little correlation.
  2. Make a low to high array for Gender by placing the cursor into cell A1, the Gender label, and choosing the A/Z down arrow icon next to the fx icon in the Standard Toolbar (row 3). Make sure the other variables also move to stay with the appropriate Gender. If they do not reposition correctly, choose Edit and Undo AutoFormat to go back to the original data display. Highlight the entire data set $1$A:$E24 and choose the A/Z down arrow icon next to the fx icon in the Standard Toolbar (row 3). Calculate the correlation coefficient for Sick Days and Performance Rating for Females and for Males. Your answer of -0.686021 for Females indicates a possible negative relationship between Sick days and Performance Rating. More on this later.
  E. Correlating several variables at one time with a Correlation Matrix
  1. It will be necessary to group the quantitative variables together by adding a column and moving Education. Create a new column by placing the cursor into column B and choose Insert and column. Highlight the Education column, choose Edit, choose Cut, place the cursor into the empty column B and choose Edit and Paste. Place the cursor into the empty column D and choose Edit, Delete, and Entire Column.
  2. Choose Tools, Data Analysis, Correlation, and OK. Insert $C$1:$E$24 as the Input Range, leave Columns checked in Grouped By as our data is arranged by column, leave Labels in First Row checked, and insert $A$26 as the Output Range. Study the result.
  F. Making a Scatter Diagram of Female Sick Days and Performance Rating
  1. Highlight columns D and E for Females and choose Chart from the Standard Toolbar (it looks like a vertical bar chart next to Z/A down arrow.
  2. Choose xy (scatter), choose Next, and choose Next.
  Insert Female Sick Days and Performance Rating Analysis into Chart Title. Insert Sick Days into Values (x) Axis and Performance Rating into Values (y) Axis, choose Next, mark As new sheet, and choose Finish.
  3. Notice that a Chart 1 tab appears at the bottom of your worksheet.
  4. Double clicking on an object such as the title of the chart will let you set characteristics like fonts and axis numeric ranges. This allows you to make the scatter diagram more attractive.
  a) Double click on the y-axis until the Format Axis screen appears and choose the Scale tab. Enter 50 in the Minimum box, 100 into the Maximum box, 50 into the Major unit box, 1 into the Minor unit box, and 50 into the Value x axis crosses at box, and choose OK.
  b. Double click on the title and adjust the Patterns, Font and Alignment to suit your taste.
  c. Every object in the graph can be adjusted to your taste or the tastes of your audience.