Assignment 2: Linear Regression
In this assignment, you will use a spreadsheet to examine pairs of variables, using the method of linear regression, to determine if t
here
is any correlation between the variables. Afterwards, you will postulate whether this correlation reveals a causal relationship (and why).
Click here to open the Excel spreadsheet containing the data for this assignment.
This spreadsheet contains the data from a study that attempted to see if there is a correlation between the hours that a student studies and the grade that they earned on a test. The correlation test you are about to run will help you to determine if there is, in fact, a correlation between study time and test score. If you find a strong correlation, then you will postulate whether you feel this indicates a causal relationship.
Below are instructions on how to perform this correlation test in Microsoft Excel.
In the Excel spreadsheet, perform the following operations:
 Save the spreadsheet to your computer.
 With your mouse, highlight all of the data on the spreadsheet in columns A and B.
 In the tabs at the top of the page, click Insert.
 In the Insert ribbon, in the Charts section, click Scatter. Be sure to select the option where it will just plot dots, it will be called Scatter with only Markers. If you do this right, then you’ll see a chart on the page.
 Now, on the chart, rightclick on one of the data points (dots). Just pick a dot somewhere near the middle of the distribution.
 Select Add Trendlinefrom the dropdown menu that appears when you rightclick on a dot.
 A new menu will appear. Select Linear, select Automatic, and click the boxes next to Display Equation on chart and Display rsquared value on chart.
 Click Close.
 Now, you should see a line drawn through the dots. It will roughly cut through the middle of the dot distribution.
 You’ll also see the linear regression equation and r2 value displayed next to the line.
To see an example spreadsheet containing a completed analysis click here.
Now that you’ve completed your analysis and determined the linear regression formula and r2, it now time to report on the results of your study and examine your findings.
In a Microsoft Word document, respond to the following:
 Report the sample you selected and the question that was explored in the study.
 Report the r2 linear correlation coefficient and the linear regression equation produced in the Excel spreadsheet.
 What would be the value of Pearson’s r (simply the square root of r2)?
 Would Pearson’s r be positive or negative? What does this imply about the relationship between the factors in this study?
 What is the implication of any correlation found between the variables in the study you picked?
 Does this correlation imply a causal relationship? Explain.
 Are there other variables that you think should have been examined that would have improved this study or help to pinpoint what factors are causal?
For this assignment, you will submit a spreadsheet and a report. The spreadsheet will be the Microsoft Excel file containing your scatterplot and analysis. Name your Microsoft Excel file as follows: LastnameFirstInitial_M3_A2.xls.
The report will be a Microsoft Word document in which you will address all of the questions in this assignment in the form of a narrative. Name your Microsoft Word document as follows: LastnameFirstInitial_M3_A2 x.
Submit both files to the M3: Assignment 2 Dropbox by Tuesday, April 9, 2013.
56
96
48
Assignment 2 Grading Criteria  Maximum Points 
Complete scatterplot and attach as an Excel file (the fraction of variation in one variable should be accounted for by variation of the other). 

Report the r2 correlation coefficient and linear regression equation with slope and intercept included and stated whether the value of r is positive or negative. 

Explain the implication of any linear relation, including its three components (scatterplot, r2 value and linear equation) found between hours spent studying, and the exam score earned.  
Total:  200 
Sheet1
3 5 80
65
94
Hours of Study  Exam score  
7  6  3  
4  60  
4  8  
2.  5  51  
16  80  
65  
1.7  53  
13  83  
12  
73  
9  
18  82  
85  
67  
69  
11.5  72  
74  
14  75  
17  78  
21  91.5  
20  94  
25  97  
22  93  
24  89  
19  90  
26  88  
27  95  
18.5  84  
25.5  
19.6  90.5 
Sheet2
Sheet3
>
Sheet
1below:
Temperature per Month, per Year
12
0
5
75
32
20
75
54
3825
50
60
70 59
40 50 60 70
90
76
7520 43 54 70
54 54 34 19
20 34 48
76 90
60
6420 34 48 62 76 90
88
45 20
Average
2
96 3 May May
1
7
2
1 2 3Exercise 1  With single formulas listed in answers 1, 2, &  3  Exercise 2  Exercise 3  Average  
Add the column of numbers  January  February  March  April  May  June  July  August  September  October  November  December  
7  5  Calculate the median of this column  20  4  21  32  43  54  6  76  87  98  60  50  
Calculate the average of this column  2005  31  42  53  64  86  97  70  38  24  
2006  25  40  59  72  48  37  26  
90  2007  30  80  100  56  34  22  
2008  19  79  88  89  
12  2009  17  62  96  81  45  29  
2010  92  67  18  
35  
85  
Add grid lines to this chart  Example 1  
78  Merge and Center the title across the months  
Select the months on the chart and make the names of the month appear vertical, see example 1  
Make the months bolded text  
Make the years bolded text  
Make a 2D column chart including all of the months and the seven years worth of data  
Answer 1  Make the entire block any shade of green with no lines  Create 12 formulas that will average each column’s data  
Answer 2  Select a few cells that are now green and make them white  
Answer 3  
Exercise 4  
Using the Essay from the first assignment, calculate the following:  
Create a formula that will tell you how many people are in the US if 1/4th of the people equals 57.7 million  
Create a formula that will calculate the total number of people in the US next year  
HINT: The increase in the total population is listed as a percentage under the “Workplace Statistics” heading  
Using the list of all of the other things people would rather do than work with a therapist  
a.  List each thing a person would rather do, one thing per cell  
b.  List the percentage associated with each, underneath the thing they would rather do (see example 2)  
c.  Create a pie chart that graphically shows preferences to therapy  
d.  Insert this chart into the essay and submit both this worksheet and the essay  
Example 2  
Surfing  Sky Diving  Tennis  Darts  Insert data into the grid below from the essay  
22%  30%  45%  3% 
Sheet1
SurfingSky Diving
Tennis
Darts
0.22
0.3
0.45
0.03
Sheet2
Surfing Sky Diving Tennis Darts 22% 30% 45% 3%
Sheet2