Question

Use the Excel directions below to create a scatterplot for the Temperature in Fahrenheit and the number of cricket chirps per sec.
Temp 88.6 71.6 92.3 85.3 80.6 75.2 69.7 81 69.4 83.3 79.6 82.6 80.6 (x)
Chirps 20.5 16 19.8 18.4 17.1 15.5 14.7 17.1 15.4 16.2 15 17.2 16 (y)
3.) Find the linear regression equation using Excel. Slope uses the equation =slope(highlight y column, highlight x column). Y-intercept uses the equation =intercept(highlight y column, highlight x column). Use the equation to create a row of predicted number of chirps.
4.) Create a row of residuals using Excel equation. Each residual answer can be found by = xvalue*slope answer + y-intercept answer.
5.) Find correlation coefficient (r ) by using the Excel equation =correl(highlight x column, highlight y column). Note here the x column comes first.
6.) State conclusion based on the correlation coefficient (r) and r- critical value (use charts in notes). Assume Ho: ρ = 0.

Answer 1

The given data entered in EXCEL .

x	y
88.6	20.5
71.6	16
92.3	19.8
85.3	18.4
80.6	17.1
75.2	15.5
69.7	14.7
81	17.1
69.4	15.4
83.3	16.2
79.6	15
82.6	17.2
80.6	16

The scatterplot for X and Y is given below:

3. Slope =SLOPE(B2:B14,A2:A14)=0.2162

Intercept=INTERCEPT(B2:B14,A2:A14)=-0.4486

slope	0.216129
intercept	-0.44856

4.The residual

x	88.6	71.6	92.3	85.3	80.6	75.2	69.7	81	69.4	83.3	79.6	82.6	80.6
y	20.5	16	19.8	18.4	17.1	15.5	14.7	17.1	15.4	16.2	15	17.2	16
Residuals	1.799501	0.9737	0.299823	0.412728	0.128536	-0.30437	0.084345	0.042084	0.849184	-1.35501	-1.75533	-0.20372	-0.97146

5. The correlation coefficient will be obtained by CORREL(A2:A14,B2:B14) formula and is =

0.842306

6.We can find the significant correlation value for the correlation coefficient at 13-2=11 df for 5% level is 0.5529. Since the calculated value of the correlation is 0.8423>the critical value, we reject the null hypothesis and conclude that the correlation coefficient is significant and not equal to zero.

The EXCEL worksheet is given below

x	y	Residuals
88.6	20.5	1.799501
71.6	16	0.9737			slope	0.216129
92.3	19.8	0.299823			intercept	-0.44856
85.3	18.4	0.412728			Correlation	0.842306
80.6	17.1	0.128536
75.2	15.5	-0.30437
69.7	14.7	0.084345
81	17.1	0.042084
69.4	15.4	0.849184
83.3	16.2	-1.35501
79.6	15	-1.75533
82.6	17.2	-0.20372
80.6	16	-0.97146
x	88.6	71.6	92.3	85.3	80.6	75.2	69.7	81	69.4	83.3	79.6	82.6	80.6
y	20.5	16	19.8	18.4	17.1	15.5	14.7	17.1	15.4	16.2	15	17.2	16
Residuals	1.799501	0.9737	0.299823	0.412728	0.12853581	-0.30437	0.084345	0.042084	0.849184	-1.35501	-1.75533	-0.20372	-0.97146