PowerPoint Presentation
Chapter 1
Excel Extension: Now You Try!
Computing Co
elations
Bauer, Fundamentals of Human Resource Management, 1e. © SAGE Publishing, 2021
1
1
Background
In this Excel Extension tutorial, you will learn how to compute a (Pearson product-moment) co
elation between two continuous variables using Microsoft Excel.
For the purposes of this exercise, imagine that the engagement and job satisfaction data for each employee were collected using an annual employee survey, and each employee’s unique ID was used to merge their survey data with their average annual customer satisfaction ratings, their supervisor-rated performance evaluation score, and the amount of annual sales revenue they generated.
For the purposes of this introductory exercise, we will ignore statistical significance, and instead practice interpreting the sign (positive or negative) and magnitude (size) of each co
elation.
Bauer, Fundamentals of Human Resource Management, 1e. © SAGE Publishing, 2021
2
Open the Excel workbook titled “Chapter 1--Excel Extension.xlsx”.
Note that the sheet includes six columns, where each column represents unique field/variable:
Unique employee ID (Employee ID)
Engagement (1 = low, 5 = high)
Job Satisfaction (1 = low, 5 = high)
Customer Satisfaction (1 = low, 7 = high)
Performance Evaluation Rating (1 = low, 10 = high)
Sales Revenue (U.S. dollars)
Note that each row below the column names represents a unique employee, where the total number of employees is 59.
Step 1
Bauer, Fundamentals of Human Resource Management, 1e. © SAGE Publishing, 2021
3
To practice, let’s compute the co
elation between Job Satisfaction and Performance Evaluation Rating.
Scroll down below the data to find empty cells.
Step 2
Bauer, Fundamentals of Human Resource Management, 1e. © SAGE Publishing, 2021
4
In a blank cell, enter the equal symbol (=) followed by the CORREL function, followed by a beginning parenthesis.
Enter the range of cell values that co
esponds to the a
ay of numeric values that fall below the Job Satisfaction variable name, which in this case is C2:C60. Note that you can enter the cell range manually by entering the first cell in the a
ay (C2), followed by a colon (:), and followed by the last cell in the a
ay (C60); alternatively, you can select the a
ay of cells by clicking and dragging your mouse.
Enter a comma (,).
Enter the range of cell values that co
esponds to the a
ay of numeric values that fall below the Performance Evaluation Rating variable name, which in this case is E2:E60. Enter an ending parenthesis, and click ENTER.
Step 3
Bauer, Fundamentals of Human Resource Management, 1e. © SAGE Publishing, 2021
5
Remember, co
elation coefficients can range from –1.00 to 1.00, where –1.00 indicates a perfect negative (inverse) linear association, 1.00 indicates a perfect positive linear association, and .00 indicates no association.
Note that the co
elation coefficient is approximately .015 (after rounding).
Regarding the sign, the co
elation coefficient is positive, which indicates a positive, linear association.
With that said, the magnitude of the co
elation coefficient is virtually zero. For our purposes, we describe the magnitude of a co
elation coefficient as follows:
.10 (negligible)
.10–.29 (small)
.30–.49 (medium)
.50–1.00 (large)
Step 4
Bauer, Fundamentals of Human Resource Management, 1e. © SAGE Publishing, 2021
6
It is generally a good practice to label each co
elation coefficient so that you can remember what association it refers to.
In a cell adjacent to the cell in which the co
elation coefficient appears, type information about the co
elation, and click ENTER.
Step 5
Bauer, Fundamentals of Human Resource Management, 1e. © SAGE Publishing, 2021
7
Sheet1
Employee ID Engagement Job Satisfaction Customer Satisfaction Performance Score Sales Revenue
EP32 3.00 3.00 3.91 2.78 60177
EP33 3.33 3.00 5.43 6.11 58914
EP34 2.67 3.67 3.98 7.92 82403
EP35 2.67 3.67 5.23 4.31 60171
EP36 3.33 3.33 4.57 2.03 45458
EP37 3.67 3.67 4.62 6.56 55568
EP38 3.00 4.00 4.07 8.95 58257
EP39 2.00 3.00 2.69 2.27 61588
EP40 3.67 3.67 7.00 2.82 41500
EP41 3.67 2.67 3.49 8.17 52654
EP42 4.00 3.67 5.96 1.00 60394
EP43 3.00 3.33 3.84 8.12 43965
EP44 3.67 4.33 6.83 4.96 63184
EP45 3.00 2.33 2.58 4.36 56528
EP46 4.67 4.00 5.50 1.00 81725
EP47 2.00 3.00 6.12 7.18 67816
EP48 4.67 4.33 7.00 5.63 68747
EP49 4.67 4.67 6.42 5.30 52062
EP50 3.33 2.67 4.53 3.32 64743
EP51 3.33 3.33 3.17 6.20 41489
EP52 3.33 3.67 4.48 8.53 65859
EP53 3.33 3.33 5.72 2.38 58289
EP54 3.67 4.33 6.11 1.00 72278
EP55 3.67 4.00 6.11 3.83 61545
EP56 4.33 4.00 4.26 9.24 48676
EP57 2.67 3.33 5.18 5.25 54567
EP58 3.33 3.00 3.76 2.13 48966
EP59 4.33 4.67 4.31 3.85 43097
EP60 4.00 4.00 5.96 6.46 57753
EP61 4.00 4.00 7.00 6.68 65517
EP62 3.00 3.67 4.79 5.00 74679
EP63 4.67 4.33 6.22 3.08 37489
EP64 3.67 4.33 5.85 7.15 48624
EP65 4.00 3.67 6.13 7.18 43493
EP66 3.67 3.00 4.70 5.06 68586
EP67 4.67 4.00 6.52 3.39 65035
EP68 2.67 3.00 5.27 8.42 46502
EP69 3.00 3.33 6.75 7.77 47626
EP70 2.67 3.67 4.60 2.58 47941
EP71 2.67 4.00 3.57 4.55 60813
EP72 4.00 5.00 3.86 4.50 76878
EP73 5.00 4.67 5.78 4.34 58443
EP74 5.00 4.00 6.82 7.20 70686
EP75 2.33 3.00 4.45 8.23 72295
EP76 4.00 4.00 6.01 4.05 59139
EP77 4.00 3.33 7.00 2.77 54572
EP78 4.67 4.67 6.68 8.10 62575
EP79 3.00 2.33 4.23 6.11 67908
EP80 3.00 3.00 4.06 3.17 61539
EP81 3.67 4.67 6.84 7.85 66603
EP82 4.33 3.33 6.14 7.61 51412
EP83 4.00 4.00 5.06 5.88 76823
EP84 3.67 3.67 6.63 6.13 36194
EP85 3.33 3.00 7.00 7.86 55104
EP86 4.33 4.67 5.15 6.32 78592
EP87 2.33 3.33 4.30 4.25 42043
EP88 3.67 3.33 3.08 4.68 68745
EP89 3.00 3.67 4.67 2.62 59975
EP90 3.33 4.00 7.00 7.34 49173
MGMT 6100
HR Analytics Assignment
1. Open the Excel workbook titled “Chapter 1--Excel Extension.xlsx” (posted on Canvas > Assignments > HR Analytics Assignment) and answer the following four questions (a through d). Be sure to interpret the sign and magnitude of the co
elations.
*If you are not sure on how to calculate co
elations, look at How to Calculate Co
elations Using Excel (posted on Canvas) for an example.
*If you did not attend Session 3, please read the section on Co
elation Does Not Equal Causation on page 28 in your textbook.
a. What is the co
elation between Employee Engagement and Sales Revenue? (5 points)
. Consider the sign and the magnitude of the co
elation. How would you describe the nature of the relation between Engagement and Sales Revenue to a manager who does not know what a co
elation coefficient means? (5 points)
c. What is the co
elation between Job Satisfaction and Customer Satisfaction? (5 points)
d. Consider the sign and the magnitude of the co
elation. How would you describe the nature of the relation between Job Satisfaction and Customer Satisfaction? (5 points)
2. Submit your answers to the four questions in one WORD document and upload it on Canvas.