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:
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:
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 
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 
>
below:
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
Surfing0.22
0.3
0.45
0.03
22% 30% 45% 3%
Sheet2