Use the XLMiner Analysis ToolPak to find descriptive statistics for Sample 1 and
ID: 3070509 • Letter: U
Question
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.
a.Sample 1 Mean: (2 decimals)
Sample 1 Standard Deviation: (2 decimals)
Sample 2 Mean: (2 decimals)
Sample 2 Standard Deviation: (2 decimals)
b. 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)
c. 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.
30 39 29 23 40 40 28 32 25 14 50 54 62 50 70 66 26 24 48 43 25 27 59 58 48 38 58 55 46 48Explanation / Answer
=Descriptive statistics using excel
a.Sample 1 Mean: (2 decimals)
sample1 mean=42.93
Sample 1 Standard Deviation: (2 decimals)
sample 1 sd=15.15
Sample 2 Mean: (2 decimals)
sample 2 mean=40.73
Sample 2 Standard Deviation:14.75
b. 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)
=COVARIANCE.S(A2:A16;B2:B16)
COVARIANCE=205.55
In cell J5, find the correlation between Sample 1 and Sample 2 using the CORREL function.
(2 decimals)
=CORREL(A2:A16;B2:B16)
=0.92
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)
=205.55/15.15*14.75
=0.92
Sample1 Sample2 Mean 42.93 Mean 40.73 Standard Error 3.91 Standard Error 3.81 Median 46.00 Median 40.00 Mode 25.00 Mode #N/A Standard Deviation 15.15 Standard Deviation 14.75 Sample Variance 229.6380952 Sample Variance 217.4952381 Kurtosis -1.277990904 Kurtosis -0.729414904 Skewness 0.245270681 Skewness -0.126977763 Range 45 Range 52 Minimum 25 Minimum 14 Maximum 70 Maximum 66 Sum 644 Sum 611 Count 15 Count 15Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.