Unit 5 Assignment: Running and Interpreting a Correlation Using Excel
Points: 100
Overview:
For this assignment, you will apply your knowledge of correlational research by running
a correlation statistic (Pearson r) using a sample data set.
Directions for Enabling Data Analysis ToolPak.
See instructions for Downloading Microsoft Office provided in Unit 4. You will need to
use the Data Analysis ToolPak feature within Excel to run the correlation. This is an
add-on feature for Excel that you need to enable manually. To enable the ToolPak,
follow the instructions below:
• Go to EXCEL
• Click on File
• Click on Options
• Click on Add-Ins
• Click on Analysis ToolPak
• Click Go
• Check Analysis ToolPak
• Click OK
• Data Analysis should appear on the upper right of your toolbar.
Instructions:
The Psychology 101 instructor is interested in determining if there is a correlation
(relationship) between hours spent studying and grades on the midterm exam. They
collect data from all 25 of their PSY101 students. Students were asked to indicate on a
survey the number of hours spent studying for the midterm exam as well as their exam
score. The data collected from students is listed below:
Hours Spent Studying Midterm Exam Score
0 59
0 49
0 39
0 47
0 45
1 61
1 79
1 63
1 67
1 72
2 87
2 84
2 86
2 88
2 85
3 92
3 94
3 91
3 92
3 91
4 100
4 100
4 100
4 97
4 98
Part A
For the data set above, run a correlation in Excel.
Directions for Running a Correlation in Excel
• Open Excel
• Enter the Data Set Above
• Go to Data
• Go to Data Analysis
• Click on Correlation
• Select your Input Range
• Select Output Range (Select Area of Cells for Graph to Appear)
• Click OK
Review the “Creating a Scatterplot and Running a Pearson r Correlation in Excel”
found in the Unit 5 Readings and Resources on Blackboard.
Then, after the following questions:
1. What is the correlation coefficient between hours spent studying and midterm
exam grades? Provide the Pearson r value. (25 points)
2. Is this a positive or negative correlation? Alternatively is there no correlation
between variables? Explain your choice. (25 points)
3. Is the correlation between variables weak moderate or strong? Explain your
answer, using the chart below to help you. (25 points)
Part B
Graph a scatter plot of the correlation. Add a title and label your Y axis and X axis
describing what the graph is showing. (25 points)
Directions for Graphing a Scatterplot
Review the “Creating a Scatterplot and Running a Pearson r Correlation in Excel” found
in the Unit 5 Readings and Resources on Blackboard.
Requirements:
• Answer all the questions in Part A and Part B.
• Submit a Word document with your answers.
Be sure to read the criteria below by which your work will be evaluated before
you write and again after you write