Question

In: Math

Sample 1 Sample 2 68 76 29 38 52 47 32 36 53 59 35 38...

 
Sample 1 Sample 2
68 76
29 38
52 47
32 36
53 59
35 38
41 36
36 24
52 52
35 40
50 44
75 86
59 69
63 77
49 49
  1. Use the XLMiner Analysis ToolPak to find descriptive statistics for Sample 1 and Sample 2. Select "Descriptive Statistics" in the ToolPak, place your cursor in the "Input Range" box, and then select the cell range A1 to B16 in the sheet. Next, place your cursor in the Output Range box and then click cell D1 (or just type D1). Finally make sure "Grouped By Columns" is selected and all other check-boxes are selected. Click OK. Your descriptive statistics should now fill the shaded region of D1:G18. Use your output to fill in the blanks below.

    Sample 1 Mean:  (2 decimals)

    Sample 1 Standard Deviation:  (2 decimals)

    Sample 2 Mean:  (2 decimals)

    Sample 2 Standard Deviation:  (2 decimals)

  2. Use a combination of native Excel functions, constructed formulas, and the XLMiner ToolPak to find covariance and correlation.

    In cell J3, find the covariance between Sample 1 and Sample 2 using the COVARIANCE.S function.

    (2 decimals)

    In cell J5, find the correlation between Sample 1 and Sample 2 using the CORREL function.
    (2 decimals)

    In cell J7, find the correlation between Sample 1 and Sample 2 algebraically, cov/(sx*sy), by constructing a formula using other cells that are necessary for the calculation.

    (2 decimals)

    Use the XLMiner Analysis ToolPak to find the correlation between Sample 1 and Sample 2. Place your output in cell I10.

    (2 decimals)

  3. Calculate z-scores using a mix of relative and absolute cell references. In cell A22, insert the formula =ROUND((A2-$E$3)/$E$7,2). Next grab the lower-right corner of A22 and drag down to fill in the remaining green cells of A23 to A36. Note how the formula changes by looking in Column D. Changing a cell from a relative reference such as E3 to an absolute reference such as $E$3 means that cell remains "fixed" as you drag. Therefore the formula you entered into A22 takes each data observation such as A2, A3, A4..., subtracts $E$3 and then divides by $E$7. Since the last two cells have absolute references they will not change as you drag. The ROUND function simply rounds the z-score to two digits.

    Now find the z-scores for Sample 2 using the same method you learned above by editing the formula to refer to the correct cells for Sample 2. Make sure each z-score is rounded to 2 places.

    Sample 2 z-scores

Solutions

Expert Solution

a.

Sample 1 Sample 2
68 76
29 38
52 47
32 36
53 59
35 38
41 36
36 24
52 52
35 40
50 44
75 86
59 69
63 77
49 49
Mean= 48.6 51.4
Standard Deviation= 13.83 18.17

Use: "=ROUND(AVERAGE(B3:B17),2)" and "=ROUND(STDEV.S(B3:B17),2)" for sample 1 and "=ROUND(AVERAGE(C3:C17),2)" and "=ROUND(STDEV.S(C3:C17),2)"

b. The covariance between Sample 1 and Sample 2=233.53.

Use "=ROUND(COVARIANCE.S(B3:B17,C3:C17),2)"

The correlation between Sample 1 and Sample 2=0.93

Use "=ROUND(CORREL(B3:B17,C3:C17),2)"

Sample 1(x) Sample 2(y) x-48.6 (x-48.6)^2 y-51.4 (y-51.4)^2 (x-48.6)(y-51.4)
68 76 19.4 376.36 24.6 605.16 477.24
29 38 -19.6 384.16 -13.4 179.56 262.64
52 47 3.4 11.56 -4.4 19.36 -14.96
32 36 -16.6 275.56 -15.4 237.16 255.64
53 59 4.4 19.36 7.6 57.76 33.44
35 38 -13.6 184.96 -13.4 179.56 182.24
41 36 -7.6 57.76 -15.4 237.16 117.04
36 24 -12.6 158.76 -27.4 750.76 345.24
52 52 3.4 11.56 0.6 0.36 2.04
35 40 -13.6 184.96 -11.4 129.96 155.04
50 44 1.4 1.96 -7.4 54.76 -10.36
75 86 26.4 696.96 34.6 1197.16 913.44
59 69 10.4 108.16 17.6 309.76 183.04
63 77 14.4 207.36 25.6 655.36 368.64
49 49 0.4 0.16 -2.4 5.76 -0.96
Total 2679.6 4619.6 3269.4
Sample mean of x= 48.6
Sample mean of y= 51.4
Sample sd of x=Sx= 13.83473888
Sample sd of y=Sy= 18.1651157
Sample cov=Sxy= 233.5285714
Correlation=r=Sxy/(SxSy)= 0.93

c.

Sample 1(x) Mean of x=Mx Standard deviation of x=Sx Z-score of x=Z=(x-Mx)/Sx Sample 2(y) Mean of y=My Standard deviation of y=Sy Z-score of y=Z=(y-My)/Sy
68 48.6 13.83 1.4 76 51.4 18.17 1.35
29 48.6 13.83 -1.42 38 51.4 18.17 -0.74
52 48.6 13.83 0.25 47 51.4 18.17 -0.24
32 48.6 13.83 -1.2 36 51.4 18.17 -0.85
53 48.6 13.83 0.32 59 51.4 18.17 0.42
35 48.6 13.83 -0.98 38 51.4 18.17 -0.74
41 48.6 13.83 -0.55 36 51.4 18.17 -0.85
36 48.6 13.83 -0.91 24 51.4 18.17 -1.51
52 48.6 13.83 0.25 52 51.4 18.17 0.03
35 48.6 13.83 -0.98 40 51.4 18.17 -0.63
50 48.6 13.83 0.1 44 51.4 18.17 -0.41
75 48.6 13.83 1.91 86 51.4 18.17 1.9
59 48.6 13.83 0.75 69 51.4 18.17 0.97
63 48.6 13.83 1.04 77 51.4 18.17 1.41
49 48.6 13.83 0.03 49 51.4 18.17 -0.13

Related Solutions

0, 3, 11, 24, 36, 47, 42, 53, 56, 59, 52, 58, 50, 64, 63, 61,...
0, 3, 11, 24, 36, 47, 42, 53, 56, 59, 52, 58, 50, 64, 63, 61, 65, 78, 89, 91 A. Calculate the mean, median, and mode. B. Which measure, mean or median, best describes the central tendency of this data? Why?
68 56 76 75 62 81 72 69 91 84 49 75 69 59 70 53...
68 56 76 75 62 81 72 69 91 84 49 75 69 59 70 53 65 78 71 87 71 74 69 65 64 You have been charged to conduct a statistical test in SPSS to verify the claim that the‘average weekly student expenses’ is different than 74 dollars using an alpha level of 5%. What is the appropriate test that is applicable in this case. Explain your reasoning. State the null and alternate hypotheses in this case using...
Math Reading 52 65 63 71 55 61 68 77 66 76 57 70 59 69...
Math Reading 52 65 63 71 55 61 68 77 66 76 57 70 59 69 76 77 69 76 59 70 74 78 73 80 62 66 59 66 72 76 61 67 55 61 73 77 Can you teach me how to solve the problem below An educator conducted an experiment to test whether new directed reading activities in the classroom will help elementary school pupils improve some aspects of their reading ability. She arranged for a third...
29; 37; 38; 40; 58; 67; 68; 69; 76; 86; 87; 95; 96; 96; 99; 106;...
29; 37; 38; 40; 58; 67; 68; 69; 76; 86; 87; 95; 96; 96; 99; 106; 112; 127; 145; 150 What is the Standard deviation (please round to two decimal places)?
Population 1 59 57 77 60 59 64 72 67 Population 2 61 53 84 53...
Population 1 59 57 77 60 59 64 72 67 Population 2 61 53 84 53 69 74 64 73 Can it be concluded, from this data, that there is a significant difference between the two population means? Let d=(Population 1 entry)−(Population 2 entry)d=(Population 1 entry)−(Population 2 entry). Use a significance level of α=0.01α=0.01 for the test. Assume that both populations are normally distributed. 1.State the null and alternative hypotheses for the test 2. Find the value of the standard...
Using basic Python 2. Given the following dictionary: speed ={'jan':47, 'feb':52, 'march':47, 'April':44, 'May':52, 'June':53, 'july':54,...
Using basic Python 2. Given the following dictionary: speed ={'jan':47, 'feb':52, 'march':47, 'April':44, 'May':52, 'June':53, 'july':54, 'Aug':44, 'Sept':54} Get all values from the dictionary and add it to a new list called speedList, but don’t add duplicates values to the new list. Your code should print out: speedList [47, 52, 44, 53, 54] 3. Given the following list of numbers: numberList = [10, 20, 33, 46, 55, 61, 70, 88, 95] First iterate the list and print only those numbers,...
Plot Nutrients added # of species 1 0 36 2 0 36 3 0 32 4...
Plot Nutrients added # of species 1 0 36 2 0 36 3 0 32 4 1 34 5 2 33 6 3 30 7 1 20 8 3 23 9 4 21 10 4 16 What effect do nutrient additions have on plant species diversity? Long-term experiments at the Rothamstead Experimental Station in the U.K. sought to investigate the relationship, with some interesting findings. The data can be found in the linked Google Sheets document  - you'll want to copy...
QUESTION 1 1. 46 172 147 76 94 80 116 150 45 47 2.      Use the...
QUESTION 1 1. 46 172 147 76 94 80 116 150 45 47 2.      Use the sample data above to answer the following question.  (See exercise 8 on page 114 of your textbook for a similar problem.) Compute s. 0.34 points    QUESTION 2 1.      136 104 109 97 71 40 109 41 119 168 2.      Use the sample data above to answer the following question.  (See exercise 8 on page 114 of your textbook for a similar problem.) Compute x' 0.33...
Population 1   Population 2 51   54 57   51 65   67 68   60 53   58 58   61...
Population 1   Population 2 51   54 57   51 65   67 68   60 53   58 58   61 75   66 72   79 Can it be concluded, from this data, that there is a significant difference between the two population means? Let d=(Population 1 entry)−(Population 2 entry). Use a significance level of α=0.1 for the test. Assume that both populations are normally distributed. Step 1 of 5: State the null and alternative hypotheses for the test. Step 2 of 5: Find the value...
Consider the following demand for Ross Home Accents’ handmade candles: Q P 1 38 2 36...
Consider the following demand for Ross Home Accents’ handmade candles: Q P 1 38 2 36 3 34 4 32 5 30 6 28 7 26 8 24 9 22 10 20 Suppose the marginal cost of producing each candles is $10. (a) Obtain the profit maximizing single price for Ross Home Accents’ candles and its profit. (b) From the table above, calculate the consumer surplus obtained by consumers of Ross’ candles. Explain why this consumer surplus constitutes a loss...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT