Please show steps to get answers in Excel.. I need to know/see the steps in exce
ID: 3178328 • Letter: P
Question
Please show steps to get answers in Excel.. I need to know/see the steps in excel that give me the answers so I understand
For the following assignment, use the Rank Correlation that was demonstrated in Chapter 12 of the textbook (page 566). Utilizing Excel
Use a rank correlation coefficient to test for a correlation between two variables.
Use a significance level of =0.05.
The new health care program in the United States makes provisions for capitation programs where health care insurers work with clinical facilities to perform risk analysis of patients to determine the cost of providing care. The following assignment might be used to assess how much a person smokes.
When nicotine is absorbed by the body, cotinine is produced. A measurement of cotinine in the body is therefore a good indicator of how much a person smokes. The reported number of cigarettes smoked per day and the measured amounts of cotinine (in ng/ml) are provided. (The values are from randomly selected subjects in a National Health Examination Survey.) Is there a significant linear correlation? How would you measure the cotinine level in the body? Explain the result.
x (cigarettes per day)
60
10
4
15
10
1
20
8
7
10
10
20
y(cotinine)
179
283
75.6
174
209
9.51
350
1.85
43.4
25.1
408
344
x (cigarettes per day)
60
10
4
15
10
1
20
8
7
10
10
20
y(cotinine)
179
283
75.6
174
209
9.51
350
1.85
43.4
25.1
408
344
Explanation / Answer
r( X,Y) =Co V ( X,Y) / S.D (X) * S.D (y)
r( X,Y) = Sum(XY) / N- Mean of (X) * Mean of (Y) / Sqrt( X^2/n - ( Mean of X)^2 ) Sqrt( Y^2/n - ( Mean of Y)^2 )
Co v ( X, Y ) = 1 /12 (37111.51) - [ 1/12 *175 ] [ 1/12 *2102.46] = 537.553
S. D ( X ) = Sqrt( 1/12*5155-(1/12*175)^2) = 14.728
S .D (Y) = Sqrt( 1/12*601709.7926-(1/12*2102.46)^2) = 139.448
r(x,y) = 537.553 / 14.728*139.448 = 0.2617
If r = 0.2617> 0 ,Positive Correlation
Coeffcient of determination = r^2 = 0.068487
b.
Given that,
value of r =0.2617
number (n)=10
null, Ho: =0
alternate, H1: !=0
level of significance, = 0.05
from standard normal table, two tailed t /2 =2.306
since our test is two-tailed
reject Ho, if to < -2.306 OR if to > 2.306
we use test statistic (t) = r / sqrt(1-r^2/(n-2))
to=0.2617/(sqrt( ( 1-0.2617^2 )/(10-2) )
to =0.77
|to | =0.77
critical value
the value of |t | at los 0.05% is 2.306
we got |to| =0.77 & | t | =2.306
make decision
hence value of |to | < | t | and here we do not reject Ho
ANSWERS
---------------
null, Ho: =0
alternate, H1: !=0
test statistic: 0.77
critical value: -2.306 , 2.306
decision: do not reject Ho