Question

4. the researcher wishes to use numerical descriptive measures to summarize the data on each of the two variables: hours worked per week and income earned per year.

1. Prepare and display a numerical summary report for each of the two variables including summary measures such as mean, median, range, variance, standard deviation, smallest and largest values and the three quartiles.                              

Notes: Use QUARTILE.EXC command to generate the three quartiles.

Compute the correlation coefficient using the relevant Excel function to measure the direction and strength of the linear relationship between the two variables. Display and interpret the correlation value.    

Data of Hours worked and yearly income as below

Yearly Income ('000's)	Hours Per Week
43.8	18
44.5	13
44.8	18
46.0	25.5
41.2	11.5
43.3	18
43.6	16
46.2	27
46.8	27.5
48.2	30.5
49.3	24.5
53.8	32.5
53.9	25
54.2	23.5
50.5	30.5
51.2	27.5
51.5	28
52.6	26
52.8	25.5
52.9	26.5
49.5	33
49.8	15
50.3	27.5
54.3	36
55.1	27
55.3	34.5
61.7	39
62.3	37
63.4	31.5
63.7	37
55.5	24.5
55.6	28
55.7	19
58.2	38.5
58.3	37.5
58.4	18.5
59.2	32
59.3	35
59.4	36
60.5	39
56.7	24.5
57.8	26
63.8	38
64.2	44.5
55.8	34.5
56.2	34.5
64.3	40
64.5	41.5
64.7	34.5
66.1	42.3
72.3	34.5
73.2	28
74.2	38
68.5	31.5
69.7	36
71.2	37.5
66.3	22
66.5	33.5
66.7	37
74.8	43.5
62.0	20
57.3	35
55.3	24
56.1	20
61.5	41

Answer 1

Let yearly income be 'X' and hours per week be 'Y'

	x			Y
1	41.2		1	11.5
2	43.3		2	13
3	43.6		3	15
4	43.8		4	16
5	44.5		1	18
6	44.8		2	18
7	46		3	18
8	46.2		4	18.5
9	46.8		5	19
10	48.2		6	20
11	49.3		7	20
12	49.5		8	22
13	49.8		9	23.5
14	50.3		10	24
15	50.5		11	24.5
16	51.2		12	24.5
17	51.5		13	24.5
18	52.6		14	25
19	52.8		15	25.5
20	52.9		16	25.5
21	53.8		17	26
22	53.9		18	26
23	54.2		19	26.5
24	54.3		20	27
25	55.1		21	27
26	55.3		22	27.5
27	55.3		23	27.5
28	55.5		24	27.5
29	55.6		25	28
30	55.7		26	28
31	55.8		27	28
32	56.1		28	30.5
33	56.2		29	30.5
34	56.7		30	31.5
35	57.3		31	31.5
36	57.8		32	32
37	58.2		33	32.5
38	58.3		34	33
39	58.4		35	33.5
40	59.2		36	34.5
41	59.3		37	34.5
42	59.4		38	34.5
43	60.5		39	34.5
44	61.5		40	34.5
45	61.7		41	35
46	62		42	35
47	62.3		43	36
48	63.4		44	36
49	63.7		45	36
50	63.8		46	37
51	64.2		47	37
52	64.3		48	37
53	64.5		49	37.5
54	64.7		50	37.5
55	66.1		51	38
56	66.3		52	38
57	66.5		53	38.5
58	66.7		54	39
59	68.5		55	39
60	69.7		56	40
61	71.2		57	41
62	72.3		58	41.5
63	73.2		59	42.3
64	74.2		60	43.5
65	74.8		61	44.5
Total	3726.3		Total	1941.8

SD = Mean =

range = Max - min

For quartiles we first arrange the data in ascending order. Then using the following formula we find the quartiles

Where Q2 is the median

Here for example if Q1 = 6.5th value then

Q1 = 6th + 0.5 (7th - 6th)

	th value
Q1	16.5
Q2	33
Q3	49.5

Yearly Income ('000's)		Hours Per Week
Mean	57.3277		Mean	29.87385
Standard Deviation	8.2670		Standard Deviation	8.045287
Sample Variance	68.3439		Sample Variance	64.72665
Range	33.6		Range	33
Minimum	41.2		Minimum	11.5
Maximum	74.8		Maximum	44.5
Q1	51.35		Q1	24.5
Q2	56		Q2	30.5
Q3	63.75		Q3	36.5

	Yearly Income ('000's) (X)	Hours Per Week (Y)	X^2	Y^2	XY
1	43.8	18	1918.44	324	788.4
2	44.5	13	1980.25	169	578.5
3	44.8	18	2007.04	324	806.4
4	46	25.5	2116	650.25	1173
5	41.2	11.5	1697.44	132.25	473.8
6	43.3	18	1874.89	324	779.4
7	43.6	16	1900.96	256	697.6
8	46.2	27	2134.44	729	1247.4
9	46.8	27.5	2190.24	756.25	1287
10	48.2	30.5	2323.24	930.25	1470.1
11	49.3	24.5	2430.49	600.25	1207.85
12	53.8	32.5	2894.44	1056.25	1748.5
13	53.9	25	2905.21	625	1347.5
14	54.2	23.5	2937.64	552.25	1273.7
15	50.5	30.5	2550.25	930.25	1540.25
16	51.2	27.5	2621.44	756.25	1408
17	51.5	28	2652.25	784	1442
18	52.6	26	2766.76	676	1367.6
19	52.8	25.5	2787.84	650.25	1346.4
20	52.9	26.5	2798.41	702.25	1401.85
21	49.5	33	2450.25	1089	1633.5
22	49.8	15	2480.04	225	747
23	50.3	27.5	2530.09	756.25	1383.25
24	54.3	36	2948.49	1296	1954.8
25	55.1	27	3036.01	729	1487.7
26	55.3	34.5	3058.09	1190.25	1907.85
27	61.7	39	3806.89	1521	2406.3
28	62.3	37	3881.29	1369	2305.1
29	63.4	31.5	4019.56	992.25	1997.1
30	63.7	37	4057.69	1369	2356.9
31	55.5	24.5	3080.25	600.25	1359.75
32	55.6	28	3091.36	784	1556.8
33	55.7	19	3102.49	361	1058.3
34	58.2	38.5	3387.24	1482.25	2240.7
35	58.3	37.5	3398.89	1406.25	2186.25
36	58.4	18.5	3410.56	342.25	1080.4
37	59.2	32	3504.64	1024	1894.4
38	59.3	35	3516.49	1225	2075.5
39	59.4	36	3528.36	1296	2138.4
40	60.5	39	3660.25	1521	2359.5
41	56.7	24.5	3214.89	600.25	1389.15
42	57.8	26	3340.84	676	1502.8
43	63.8	38	4070.44	1444	2424.4
44	64.2	44.5	4121.64	1980.25	2856.9
45	55.8	34.5	3113.64	1190.25	1925.1
46	56.2	34.5	3158.44	1190.25	1938.9
47	64.3	40	4134.49	1600	2572
48	64.5	41.5	4160.25	1722.25	2676.75
49	64.7	34.5	4186.09	1190.25	2232.15
50	66.1	42.3	4369.21	1789.29	2796.03
51	72.3	34.5	5227.29	1190.25	2494.35
52	73.2	28	5358.24	784	2049.6
53	74.2	38	5505.64	1444	2819.6
54	68.5	31.5	4692.25	992.25	2157.75
55	69.7	36	4858.09	1296	2509.2
56	71.2	37.5	5069.44	1406.25	2670
57	66.3	22	4395.69	484	1458.6
58	66.5	33.5	4422.25	1122.25	2227.75
59	66.7	37	4448.89	1369	2467.9
60	74.8	43.5	5595.04	1892.25	3253.8
61	62	20	3844	400	1240
62	57.3	35	3283.29	1225	2005.5
63	55.3	24	3058.09	576	1327.2
64	56.1	20	3147.21	400	1122
65	61.5	41	3782.25	1681	2521.5
Total	3726.3	1941.8	217994.19	62151.54	114153.68

Excel function for correlation coefficient is 'correl(array x,array y)'

Manually correlation coefficient =

Since 'r' is positive and 0.5< r <1

There is strong positve relation between yearly income and weekly hours.