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 Use the XLMiner Analysis ToolPak to find descriptive statistics for Sample 1 and...

Free

90.2K

Verified Solution

Question

Basic Math

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

Use the XLMiner Analysis ToolPak to find descriptive statisticsfor Sample 1 and Sample 2. Select "Descriptive Statistics" in theToolPak, place your cursor in the "Input Range" box, and thenselect the cell range A1 to B16 in the sheet. Next, place yourcursor in the Output Range box and then click cell D1 (or just typeD1). Finally make sure "Grouped By Columns" is selected and allother check-boxes are selected. Click OK. Your descriptivestatistics should now fill the shaded region of D1:G18. Use youroutput 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, constructedformulas, and the XLMiner ToolPak to find covariance andcorrelation.
In cell J3, find the covariance between Sample 1 and Sample 2using the COVARIANCE.S function.
(2 decimals)
In cell J5, find the correlation between Sample 1 and Sample 2using the CORREL function.
(2 decimals)
In cell J7, find the correlation between Sample 1 and Sample 2algebraically, cov/(sx*sy), by constructing a formula using othercells that are necessary for the calculation.
(2 decimals)
Use the XLMiner Analysis ToolPak to find the correlation betweenSample 1 and Sample 2. Place your output in cell I10.
(2 decimals)
Calculate z-scores using a mix of relative and absolute cellreferences. In cell A22, insert the formula=ROUND((A2-$E$3)/$E$7,2). Next grab the lower-right corner of A22and drag down to fill in the remaining green cells of A23 to A36.Note how the formula changes by looking in Column D. Changing acell from a relative reference such as E3 to an absolute referencesuch as $E$3 means that cell remains "fixed" as you drag. Thereforethe formula you entered into A22 takes each data observation suchas A2, A3, A4..., subtracts $E$3 and then divides by $E$7. Sincethe last two cells have absolute references they will not change asyou drag. The ROUND function simply rounds the z-score to twodigits.
Now find the z-scores for Sample 2 using the same method youlearned above by editing the formula to refer to the correct cellsfor Sample 2. Make sure each z-score is rounded to 2 places.
Sample 2 z-scores

Answer & Explanation Solved by verified expert

3.8 Ratings (562 Votes)

	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

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

Get Answers to Unlimited Questions

Join us to gain access to millions of questions and expert answers. Enjoy exclusive benefits tailored just for you!

Membership Benefits:

Unlimited Question Access with detailed Answers
Zin AI - 3 Million Words
10 Dall-E 3 Images
20 Plot Generations
Conversation with Dialogue Memory
No Ads, Ever!
Access to Our Best AI Platform: Flex AI - Your personal assistant for all your inquiries!

Become a Member

Sample 2 z-scores