Linear Regression

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:

1. Save the spreadsheet to your computer.
2. With your mouse, highlight all of the data on the spreadsheet in columns A and B.
3. In the tabs at the top of the page, click Insert.
4. 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.
5. Now, on the chart, right-click on one of the data points (dots). Just pick a dot somewhere near the middle of the distribution.
6. Select Add Trendlinefrom the drop-down menu that appears when you right-click on a dot.
7. A new menu will appear. Select Linear, select Automatic, and click the boxes next to Display Equation on chart and Display r-squared value on chart.
8. Click Close.
9. Now, you should see a line drawn through the dots. It will roughly cut through the middle of the dot distribution.
10. 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:

1. Report the sample you selected and the question that was explored in the study.

1. Report the r2 linear correlation coefficient and the linear regression equation produced in the Excel spreadsheet.

1. What would be the value of Pearson’s r (simply the square root of r2)?

1. Would Pearson’s r be positive or negative? What does this imply about the relationship between the factors in this study?

1. What is the implication of any correlation found between the variables in the study you picked?

1. Does this correlation imply a causal relationship? Explain.

1. 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

6

65

4

8

9

12

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

2

>

Sheet

1

below:

Temperature per Month, per Year

1

2

0

5

75

32

87 3

20

75

54

38

25

50

60

70 59

40 50 60 70

90

76

75

20 43 54 70

54 54 34 19

20 34 48

76 90

60

64

20 34 48 62 76 90

88

45 20

Average

65 1

2

96 3

May May

65 4

45 5

6

1

7

2

1

2

3

																																																																																																																																																																																																																																																																														Exercise 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

Surfing
Sky Diving
Tennis
Darts

0.22
0.3
0.45
0.03

Sheet2

Surfing Sky Diving Tennis Darts

22% 30% 45% 3%

Sheet2

Sheet3