Question

Bivariate Data & Probability

After completing the calculation by hand in Q1 you can use Excel to check your answers before submitting. Q2 assesses your general understanding of probability and linear associations using Excel to analyse a large dataset.

Question 1       Covariance and Correlation

The table below shows a set of sample bivariate data. Calculate the covariance and correlation coefficient by completing the below table. Show all working.

X	Y	(X - )	(Y - )	(X - )(Y - )
3	7
1	9
5	6
6	3
5	0

Note: To compute sample means and sample standard deviations, you can use Excel or a calculator; no working is required.

Question 2       Science Majors (II)

In THA 1 the Science Department of a Queensland university analysed the distribution of GPA of their science majors at the end of their first year of study. They also wanted to investigate how students’ high school scores in Maths (HS_MATH), Science (HS_SCI) and English (HS_ENG), their gender (GENDER) and their parents’ highest level of education (PARENT EDUCATION) relate to GPA. The dataset is contained in the file: GPA (THA 2).xlsx. Based on the dataset, answer the following questions.

(a)        Using the Correlation function in Data/Data Analysis, construct a correlation matrix for the quantitative variables: GPA, HS_MATH, HS_SCI and HS_ENG, and comment on the direction and strength of linear association for each of the six (6) correlation coefficients.

(b)        To investigate if parent’s education and gender of student are related, we can use the Pivot Table in Excel to cross tabulate GENDER and PARENT EDUCATION. Copy and paste the pivot table output onto your answer sheet and use the table to answer the following questions.

(i)        If we randomly select a student what is the probability that the student has parents with secondary or below education?

(ii)        Given the student is male, what is the probability that the student has parents with secondary or below education?

(iii)       What is the probability that a female student has parents with secondary or below education?

(iv)       Would you say PARENT EDUCATION and GENDER are dependent or independent? Explain briefly.

Question 2 continues overleaf.

Question 2 (cont.)

(c)        Do you think there is gender difference in students’ academic performance? Would students with university educated parent have higher GPA? We wish to investigate if student’s GPA is related to gender and parent’s education. You are asked to use the Pivot Table to find out:

(i)         The average GPA of male and female students, and

(ii)        The average GPA of students with parents with Post-graduate, University and Secondary and below education.

For part (c) you are only required to copy and paste the Excel pivot tables on to your document. No explanation or working is required.

Student ID	GPA	HS_MATH	HS_SCI	HS_ENG	PARENT EDUCATION	GENDER
1	6.75	10	10	10	P	M
2	3.78	6	6	6	S	M		LEGEND
3	4.11	8	6	8	U	M		P = Postgraduate Education
4	3.64	9	10	7	U	M		U = University Education
5	5.91	8	9	8	U	M		S = Secondary or below Edcuation
6	5.75	10	8	8	U	M
7	5.35	8	10	10	S	M
8	1.99	10	10	7	S	M
9	4.81	10	10	10	P	M
10	4.09	7	7	6	U	M
11	5.39	9	10	6	U	M
12	5.84	5	9	7	U	M
13	1.59	6	5	7	S	M
14	2.50	10	9	9	U	M
15	4.09	8	9	7	S	M
16	6.52	10	10	9	U	M
17	6.65	10	10	9	U	M
18	7.00	9	9	8	U	M
19	3.50	9	6	5	U	M
20	5.25	10	10	9	P	M
21	4.06	9	7	8	U	M
22	4.58	9	8	7	P	M
23	5.37	7	4	7	S	M
24	5.18	9	7	6	U	M
25	6.56	10	9	9	U	M
26	5.46	10	10	7	S	M
27	3.93	9	7	4	S	M
28	5.30	8	8	7	S	M
29	1.35	7	6	6	S	M
30	5.60	9	5	7	P	M
31	2.45	6	8	8	S	M
32	4.60	10	10	6	S	M
33	3.36	9	10	8	S	M
34	5.12	10	10	10	U	M
35	6.54	9	10	9	P	M
36	4.95	10	9	9	U	M
37	5.42	9	10	9	U	M
38	4.20	7	6	6	S	M
39	4.88	9	6	7	S	M
40	3.37	8	6	8	S	M
41	3.79	10	7	7	U	M
42	3.13	7	7	5	S	M
43	5.86	10	10	10	U	M
44	6.31	10	10	9	P	M
45	4.81	10	7	5	U	M
46	6.26	10	7	8	P	M
47	4.83	10	10	10	P	M
48	6.42	10	10	10	U	M
49	6.66	10	10	7	U	M
50	5.53	10	9	8	S	M
51	5.53	9	7	7	P	M
52	6.33	10	10	8	U	M
53	3.93	6	9	10	S	M
54	4.46	7	8	8	U	M
55	4.93	10	9	9	U	M
56	5.68	9	7	8	U	M
57	3.71	7	7	8	S	M
58	3.29	10	6	6	S	M
59	4.63	8	10	8	S	M
60	3.22	9	6	6	S	M
61	3.86	7	7	8	S	M
62	5.18	9	7	8	P	M
63	4.23	6	6	8	S	M
64	5.96	9	4	7	P	M
65	5.81	10	10	10	P	M
66	4.74	8	7	9	U	M
67	5.95	9	10	9	U	M
68	4.34	8	9	6	S	M
69	4.34	8	8	7	U	M
70	1.00	7	10	9	P	M
71	5.56	9	10	8	P	M
72	5.95	7	8	4	S	M
73	5.61	8	8	7	S	M
74	4.72	6	8	6	S	M
75	5.35	8	6	5	U	M
76	7.00	9	10	10	U	M
77	6.12	8	7	8	S	M
78	4.91	9	7	4	U	M
79	3.16	9	9	9	P	M
80	6.47	10	10	10	U	M
81	5.04	9	7	6	S	M
82	4.62	9	9	8	U	M
83	5.40	10	10	8	U	M
84	4.93	4	5	7	S	M
85	5.19	10	10	10	U	M
86	4.91	10	10	10	U	M
87	5.81	10	9	10	U	M
88	5.61	7	9	8	S	M
89	6.47	8	10	8	S	M
90	5.35	5	9	9	S	M
91	5.02	9	9	9	S	M
92	3.71	7	6	7	P	M
93	3.95	5	7	7	U	M
94	3.55	6	7	9	U	M
95	4.25	7	10	10	U	M
96	4.37	10	9	9	S	M
97	2.90	8	4	3	U	M
98	5.96	9	9	9	U	M
99	3.41	7	8	9	S	M
100	5.82	7	6	7	U	M
101	2.78	8	9	7	S	M
102	4.30	6	7	7	U	M
103	6.02	10	10	9	S	M
104	2.95	8	7	7	S	M
105	1.30	9	7	7	U	M
106	4.88	8	8	7	P	M
107	1.00	4	6	6	S	M
108	3.57	8	7	7	S	M
109	3.50	6	5	6	S	M
110	2.34	6	7	8	U	M
111	4.02	9	10	10	P	M
112	5.70	10	10	9	P	M
113	5.49	9	8	9	U	M
114	3.93	10	10	10	U	M
115	1.75	8	9	10	U	M
116	4.86	9	9	10	S	M
117	3.95	6	8	5	S	M
118	3.69	6	9	9	S	M
119	4.95	6	7	7	S	M
120	1.00	5	7	7	S	M
121	4.37	9	9	9	U	M
122	4.77	9	8	7	S	M
123	4.27	8	8	8	U	M
124	1.94	7	7	7	U	M
125	5.46	10	10	10	U	M
126	3.79	8	7	8	U	M
127	3.50	3	7	6	S	M
128	3.18	6	8	6	S	M
129	5.63	9	7	9	U	M
130	5.39	9	10	8	S	M
131	1.40	8	10	9	S	M
132	4.51	10	9	9	U	M
133	3.76	6	6	6	S	M
134	4.37	7	10	10	U	M
135	3.23	10	8	7	U	M
136	1.87	7	8	6	S	M
137	5.16	9	9	8	U	M
138	6.51	7	8	7	U	M
139	1.21	6	6	7	S	M
140	4.67	9	9	10	U	M
141	4.39	9	8	7	U	M
142	2.55	7	7	8	S	M
143	4.18	6	5	6	S	M
144	5.25	4	3	4	S	M
145	5.86	10	10	10	P	M
146	4.27	10	9	9	S	F
147	5.86	9	9	9	U	F
148	6.65	10	9	8	U	F
149	4.56	9	7	8	P	F
150	4.90	10	9	9	P	F
151	5.74	10	10	10	P	F
152	5.02	8	8	7	U	F
153	5.53	8	9	8	U	F
154	5.37	9	8	9	U	F
155	6.44	10	8	9	U	F
156	5.84	10	9	10	U	F
157	6.05	9	9	8	S	F
158	4.25	9	5	9	U	F
159	4.84	6	5	9	P	F
160	6.40	10	10	10	U	F
161	3.25	7	9	7	S	F
162	3.41	6	6	9	S	F
163	5.35	10	10	10	U	F
164	3.39	8	8	8	S	F
165	5.49	9	9	10	U	F
166	4.98	10	8	8	U	F
167	6.07	10	10	9	U	F
168	5.93	10	10	10	S	F
169	6.82	10	10	10	U	F
170	6.38	9	9	9	U	F
171	6.37	8	6	8	S	F
172	4.83	10	10	10	S	F
173	5.00	10	9	10	U	F
174	4.39	8	9	10	U	F
175	5.00	8	9	8	U	F
176	5.84	10	9	9	U	F
177	5.82	9	7	9	U	F
178	6.45	10	10	8	U	F
179	3.15	7	7	7	U	F
180	4.49	9	10	10	U	F
181	3.99	8	10	10	U	F
182	3.50	2	4	6	S	F
183	4.42	8	9	8	S	F
184	2.95	7	6	7	U	F
185	5.35	9	10	9	U	F
186	4.81	8	9	8	U	F
187	4.58	9	10	8	U	F
188	3.74	5	4	8	U	F
189	3.37	10	8	8	S	F
190	5.00	9	4	8	S	F
191	4.14	8	7	9	U	F
192	2.18	7	8	6	U	F
193	5.56	10	10	10	P	F
194	5.77	10	10	9	S	F
195	4.00	7	6	8	S	F
196	4.74	9	7	10	U	F
197	4.53	10	10	10	U	F
198	5.93	9	9	10	P	F
199	4.28	7	7	8	S	F
200	4.53	5	4	7	S	F
201	4.51	10	10	9	P	F
202	4.70	8	6	9	U	F
203	4.42	7	6	9	S	F
204	5.25	10	8	9	U	F
205	3.62	9	7	6	S	F
206	3.23	10	8	10	U	F
207	5.84	10	9	10	U	F
208	4.76	6	5	7	P	F
209	7.00	10	10	10	U	F
210	5.95	6	9	9	P	F
211	5.12	9	9	10	P	F
212	3.83	6	5	6	S	F
213	5.81	10	9	10	S	F
214	5.96	8	6	8	S	F
215	3.15	8	7	9	U	F
216	2.66	9	9	10	U	F
217	4.16	9	9	10	P	F
218	3.65	9	7	8	S	F
219	5.65	10	10	10	S	F
220	2.31	9	8	9	S	F
221	4.06	6	6	7	S	F
222	1.48	7	7	9	S	F
223	2.80	4	7	7	S	F
224	3.99	9	8	9	U	F

Answer 1

SOLUTION1 :

Sum(X) =3 + 1 + 5 + 6 + 5 = 20 XMean = 4 Sum(Y) =7 + 9 + 6 + 3 + 0 = 25 YMean = 5 Covariance(X,Y) = SUM(xi - xmean)*(yi - ymean)/(samplesize -1) = (3-4)*(7-5)+(1-4)*(9-5)+(5-4)*(6-5)+(6-4)*(3-5)+(5-4)*(0-5))/4 = -5.5 X Values ∑ = 20 Mean = 4 ∑(X - Mx)2 = SSx = 16 Y Values ∑ = 25 Mean = 5 ∑(Y - My)2 = SSy = 50 X and Y Combined N = 5 ∑(X - Mx)(Y - My) = -22 R Calculation r = ∑((X - My)(Y - Mx)) / √((SSx)(SSy)) r = -22 / √((16)(50)) = -0.7778 NOTE: REPOST QUESTION 2 ALONG WITH THE DATA. THANK YOU!