In: Statistics and Probability
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 subtracting the observed value and the predicted value.
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.
I NEED HELP TYPING AND SOLVING THIS ON AN EXCEL SHEET PLEASE!!!!
ANSWER:
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 | |
NOTE:: I hope this answer is helpfull to you......**Please suppport me with your rating
**Please give me"LIKE".....Its very important for me......THANK YOU