Don S. Christensen Shoreline Community College
Psyc 209 Psychology Department
HOMEWORK #1: EXCEL CORRELATION ASSIGNMENT (22 Points)
Due Date: Thursday October 13th in class
Purpose:
This assignment will increase your understanding of the concept of “correlation” and provides practice using Microsoft Excel to enter data, calculate descriptive statistics, calculate correlations, and create and interpret scatter plots.
Suggested Time Table:
Information relative to this assignment will be covered in week 2, so I don’t expect you to work on it right away. You may already be familiar with Excel. If not, don’t worry. One of the goals of this assignment is to introduce you to Excel. However, the assumption of this assignment is that students have not calculated correlations or created scatterplots using this application.
Late Assignments:
Assignments handed in after the due date listed above will lose 2 points per day late.
Suggested Formatting:
Please staple together the following:
Page 1: Excel printout of Data Set1 with your Excel computations and scatterplot (see next page of this assignment for sample output).
Page 2: Excel printout of Data Set 2 with your Excel computations and scatterplot.
Page 3: Hand calculation of correlation for Data Set 1 and short answers to Data Set 1 question 9 and Data Set number 2 question 9 (please use Calculation and Answer Worksheet provided on the last page of the assignment)
DATA SET 1
General Scenario: Dr. Truong wants to examine the relation between the amount of television that people watch and how happy they are. Ten adults participate in her study. Each participant records the total number of hours of TV that he or she watches for a particular week. In addition, at the end of the week, each participant completes a questionnaire that measures the person’s overall happiness. The questionnaire scores can range from 0(extremely unhappy) to 10 (extremely happy). The results from Dr. Truong’s experiment are listed below.
Participant Number / Hours of TV Watched / Happiness Score1 / 8 / 4
2 / 5 / 6
3 / 3 / 9
4 / 3 / 8
5 / 0 / 10
6 / 1 / 10
7 / 6 / 5
8 / 4 / 8
9 / 5 / 5
10 / 5 / 5
Directions for Data Set 1:
1. Enter the data from the table above into an Excel spreadsheet. Label row 1 of columns A, B, and C with “Participant Number,” “Hours of TV Watched,” and “Happiness Score,” respectively.
2. In the “Participant Number column,” in cells 14 through 18 respectively, type in the following terms: mean, median, mode, variance, standard deviation, and Pearson r.
3. Use Excel to compute the mean, median, mode, variance, and standard deviation (round to two decimal places). NOTE: Excel has a several ways of calculating the standard deviation & variance. Make sure that you have selected the correct ones by calculating the standard deviation of Hours of TV Watched by hand and compare your calculation with Excel’s. If it doesn’t match, change your formula.
4. Use Excel to compute the Pearson correlation coefficient between hours of TV watched and happiness (round to two decimal places).
5. Use Excel to create a scatterplot for these two variables. Label the X-axis “Hours of TV Watched” and the Y-axis “Happiness Score,” and make sure that the data along each axis correspond to the correct variable (e.g., make sure that data plotted on an axis matches your label for it).
6. Enter your name as part of the X-axis label. To do this, create the basic table first. After you have positioned it in your spreadsheet, click on the label (once to select it) and insert your cursor after the last letter. Hit “Return” and then type your name.
7. Add a trendline to your chart. This will visually illustrate the line the correlation is attempting to fit to the data. To do this, select your chart by clicking on it once. The “Chart” menu will appear above at the top of the page, in between the “Tools” and “Window” menus. The Chart menu will not appear if you don’t select your chart. You will see the “Table” menu instead and you won’t be able to create a trendline. With your chart selected, click on (or pull down) the “Chart” menu and select “Add Trendline…” You want a linear trendline, which is usually the default setting. If “Linear” is already highlighted, click “OK” and a trendline line will be added to your chart.
8. Print out a page containing your calculations and scatter plot (7 points).
9. Using the scatterplot and correlation, describe the association between the two variables in a sentence or two. (Answer this question in the space provided on the Calculation and Answer Worksheet.)
10. Calculate the Pearson correlation coefficient by hand, using the definitional formulas provided with this assignment. Please use the work sheet provided. You may use a calculator but please SHOW YOUR WORK. Make sure your calculation matches the number calculated by Excel.
DATA SET 2
General Scenario: Dr. Rouke wants to examine the relation between arousal and performance on an athletic task: freethrow shooting in basketball. Ten adults have agreed to participate in his study. Prior to shooting freethrows, each participant completes a physiological test that measures feelings of arousal. Scores range from 0 (low arousal) to 9 (high arousal). After this arousal test, each participant shoots 10 freethrows and the number of baskets made is recorded. The results from Dr. Rouke’s study are listed below.
Participant Number / Arousal Score / Freethrow Performance1 / 6 / 6
2 / 2 / 4
3 / 5 / 6
4 / 9 / 2
5 / 2 / 3
6 / 3 / 5
7 / 1 / 2
8 / 8 / 4
9 / 7 / 5
10 / 4 / 6
Directions for Data Set 2;
1. Enter the data from the table above into an Excel spreadsheet. Label columns A, B, C with “Participant Number,” “Arousal Score,” and “Freethrow Performance” respectively.
2. In the Participant Number column, in cells 14 through 18 respectively, type in the following terms: mean, median, mode, variance, standard deviation, and Pearson r.
3. Use Excel to compute the mean, median, mode, variance, and standard deviation (round to two decimal places).
4. Use Excel to compute the Pearson correlation coefficient between arousal and freethrow performance (round to two decimal places).
5. Use Excel to create a scatterplot for these two variables. Label the X-axis “Arousal” and the Y-axis “Freethrow Performance,” and make sure that the data along each axis correspond to the correct variable (e.g., make sure that data plotted on an axis matches your label for it).
6. Enter your name directly underneath the X-axis label (see Data Set 1 for instructions on how to do this).
7. Add a trendline to your chart (see Data Set 1 for instructions on how to do this).
8. Print out a page containing your calculations and scatter plot (7 points).
9. Based solely on the Pearson correlation coefficient, what would you conclude about the relation between arousal and freethrow performance? Based on the scatterplot, what conclusion do you reach? Why does the Pearson statistic provide an inaccurate picture in this particular example? (Answer this question in the space provided on the Calculation and Answer Worksheet.)
Sample Pearson Correlation Coefficient Calculation
Legend
N = number of participants (in this example, N = 10 participants)
X = variable corresponding to the numbers in column X (e.g., “self-esteem” measured on 0-10 scale)
Y = variable corresponding to the numbers in column Y (e.g., “creativity” measured on a 0-10 scale)
Xi = any individual number in column X (e.g., 8 or 7 or 6….)
Yi = any individual number in column Y (e.g., 9 or 6 or 4…)
MX = the mean or average of variable X
MY = the mean or average of variable Y
SDX = the standard deviation of variable X
SDY = the standard deviation of variable Y
Pearson r = Covariance ¸ (SDX´SDY) (Steps 1-4 below will show you how to calculate the covariance)
Step 1: Calculate the Mean of X and the Mean of Y.
Mean of X = MX = 60/10 = 6; Mean of Y = MY = 50/10 = 5
Step 2: Calculate the variance and the standard deviation of X and Y.
Variance of X = 54/10 = 5.4 Standard Deviation of X = SDX = 2.32
Variance of Y = 64/10 = 6.4 Standard Deviation of Y = SDY = 2.53
Step 3: Calculate the Covariance of X and Y.
CovarianceXY = Average cross-product = (Sum of Column H) ¸ N = 49/10 = 4.9
Step 4: Calculate the Pearson r.
Pearson r = Covariance ¸ (SDX´SDY) = 4.9 ¸ (2.32´2.5) = 0.83
A / B / C / D / E / F / G / HParticipant / X
(self-esteem) / Y
(creativity) / (Xi-MX) / (Xi-MX)2 / (Yi-MY) / (Yi-MY)2 / (Xi-MX)(Yi-MY)
1 / 8 / 9 / 2 / 4 / 4 / 16 / 2 ´ 4 = 8
2 / 7 / 6 / 1 / 1 / 1 / 1 / 1 ´ 1 = 1
3 / 6 / 4 / 0 / 0 / -1 / 1 / 0 ´ -1 = 0
4 / 5 / 2 / -1 / 1 / -3 / 9 / -1 ´ -3 = 3
5 / 6 / 6 / 0 / 0 / 1 / 1 / 0 ´ 1 = 0
6 / 1 / 2 / -5 / 25 / -3 / 9 / -5 ´ -3 = 15
7 / 8 / 8 / 2 / 4 / 3 / 9 / 2 ´ 3 = 6
8 / 3 / 1 / -3 / 9 / -4 / 16 / -3 ´ -4 = 12
9 / 9 / 6 / 3 / 9 / 1 / 1 / 3 ´ 1 = 3
10 / 7 / 6 / 1 / 1 / 1 / 1 / 1 ´ 1 = 1
Sum / 60 / 50 / 0 / 54 / 0 / 64 / 49
Average / 6.0 / 5.0 / 0.0 / 5.4 / 0.0 / 6.4 / 4.9
Calculation and Answer Worksheet
Data Set 1:
9. Describe the association between the TV watching and happiness. Don’t just say that the correlation is positive or negative. Explain what your correlation tells you about how these two variables are associated with each other. (2 points)
10. Calculate the Pearson r by hand. Enter numbers from Data Set #1 in the table below. See “Sample Pearson Correlation Coefficient Calculation” on the previous page for additional instructions. (2 points)
Participant / X / Y / (Xi-MX) / (Xi-MX)2 / (Yi-MY) / (Yi-MY)2 / (Xi-MX)(Yi-MY)
Sum
Average
Show additional calculations below:
Pearson r = ______
Data Set 2:
9. Based solely on the Pearson correlation coefficient, what would you conclude about the relation between arousal and freethrow performance? Based on the scatterplot, what conclusion do you reach? Why does the Pearson statistic provide an inaccurate picture in this particular example? (2 points)