Sample 1
Sample 2
38
37
48
39
26
31
71
73
35
47
73
60
48
60
63
72
56
52
75
61
62
75
39
44
68
55
47
54
32
30
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 checkboxes 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)
______________
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) ______________
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