PSY341 – Research Methods in the

Social Sciences

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.

© 2022 Post University, Waterbury, CT

ALL RIGHTS RESERVED

Evaluation Rubric for Unit 5 Excel Assignment

CRITERIA Deficient Needs

Improvement

Proficient Exemplary

0 Points 25 Points

Correlation

Coefficient

(Pearson r

Statistic)

The correlation

coefficient (Pearson

r statistic) is not

stated OR is stated

but incorrect.

N/A N/A The correlation

coefficient (Pearson

r statistic) is stated

and correct.

0 points 1 – 11 points 12 – 24 points 25 points

Direction of

Correlation

The direction of the

correlation (positive,

negative, or no

correlation) is not

given nor is it

explained. OR The

direction is given but

the explanation is

inaccurate.

The direction of

the correlation

(positive, negative,

or no correlation)

is accurate, but an

explanation is not

provided.

The direction of the

correlation

(positive, negative,

or no correlation) is

explained and is

accurate, but the

explanation is

somewhat lacking

in detail.

The direction of the

correlation (positive,

negative, or no

correlation) is

thoroughly

explained, and the

explanation is

accurate.

0 points 1 – 11 points 12 – 24 points 25 points

Strength of

Correlation

The strength of the

correlation (weak,

moderate, or strong)

is not given nor is it

explained. OR The

strength is given but

the explanation is

inaccurate.

The strength of the

correlation (weak,

moderate, or

strong) is accurate,

but an explanation

is not provided.

The strength of the

correlation (weak,

moderate, or

strong) is explained

accurately, but the

explanation is

somewhat lacking

in detail.

The strength of the

correlation (weak,

moderate, or strong)

is thoroughly

explained, and the

explanation is

accurate.

0 points 1 – 11 points 12 – 24 points 25 points

Graph of

Correlation

Graph of correlation

is not provided or is

provided but does

not accurately

illustrate the

correlation between

variables and is

missing all important

components such as

a title and labeled

axes.

Graph of

correlation is

provided and

accurately

illustrates the

correlation

between variables.

Graph is missing

most of the

important

components, such

as a title and

labeled axes.

Graph of

correlation is

provided and

accurately

illustrates the

correlation between

variables. Graph is

missing some

important

components, such

as a title, and/or

labeled axes.

Graph of correlation

is provided and

accurately illustrates

the correlation

between variables.

Graph includes a

title and labeled

axes.

- Overview:
- Directions for Enabling Data Analysis ToolPak.
- Instructions:
- Part A
- Directions for Running a Correlation in Excel
- Part B
- Directions for Graphing a Scatterplot
- Requirements:
- Evaluation Rubric for Unit 5 Excel Assignment