Question

Bivariate Data & Probability After completing the calculation by hand in Q1 you can use Excel...

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

Homework Answers

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!

Know the answer?
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for?
Ask your own homework help question
Similar Questions
a survey was conducted on sky divers attitudes toward the practise example activities. They were asked...
a survey was conducted on sky divers attitudes toward the practise example activities. They were asked to state the extent to which they found the example activities helpful for their learning in sky diving on a 7 point scale (1 = strongly disagree; 4 = neutral; 7 = strongly agree). Gender and Lecture Attendance in sky diving were also recorded. A random sample of 60 students was selected Rating_catwas created to re-group the simulation ratings into three categories: Disagree (1,...
a survey was conducted on sky divers attitudes toward the practise example activities. They were asked...
a survey was conducted on sky divers attitudes toward the practise example activities. They were asked to state the extent to which they found the example activities helpful for their learning in sky diving on a 7 point scale (1 = strongly disagree; 4 = neutral; 7 = strongly agree). Gender and Lecture Attendance in sky diving were also recorded. A random sample of 60 students was selected Rating_catwas created to re-group the simulation ratings into three categories: Disagree (1,...
The table below shows a set of bivariate data: X and Y. Calculate the covariance and...
The table below shows a set of bivariate data: X and Y. Calculate the covariance and correlation coefficients by completing the below table, assuming sample data. Show all workings. (Note: You can calculate the mean and standard deviation of X & Y with Excel or your calculator; no working for their calculation is required.) X Y (X - X bar) (Y - Y bar) (X - X bar)(Y - Y bar) 5 5 -0.2 -0.6 0.12 2 3 -3.2 -2.6...
a survey was conducted on sky divers attitudes toward the practise example activities. They were asked...
a survey was conducted on sky divers attitudes toward the practise example activities. They were asked to state the extent to which they found the example activities helpful for their learning in sky diving on a 7 point scale (1 = strongly disagree; 4 = neutral; 7 = strongly agree). Gender and Lecture Attendance in sky diving were also recorded. A random sample of 60 students was selected Rating_catwas created to re-group the simulation ratings into three categories: Disagree (1,...
Can someone who knows how to use excel forecast my data and give me exhange rates...
Can someone who knows how to use excel forecast my data and give me exhange rates for Y6Q1, Y6Q2, Y6Q3, and Y6Q4? Also, once the results are added for year 6, can I have a scatter plot with the line going across be done with all the information provided with the information given? Quarter X    Y=Exchange Rate Y3Q1    1 6.07 Y3Q2    2 6.30 Y3Q3    3 5.73 Y3Q4    4 5.57 Y4Q1    5 5.60 Y4Q2   ...
A Poisson distribution has λ = 4.7.   (a )[2] Use the Excel function POISSON.DIST() and 5...
A Poisson distribution has λ = 4.7.   (a )[2] Use the Excel function POISSON.DIST() and 5 decimals to fill in the following table: 4 x 0 1 2 3 4 5 6 7 8 9 10 11 12+ P(x) (b)[2] Use a column chart to visualize the probability distribution above. How is it skewed? (c)[1] Find P(x ≤ 3). [steps & result] (d)[1] Find P(x ≥ 7). [steps & result] (e)[2] Find P(5 < x ≤ 9). [steps & result]...
Using the class sample data, analyze the student heights by completing the following. Please note the...
Using the class sample data, analyze the student heights by completing the following. Please note the following directions. The data below was collected from a group of 45 female students last semester. You will use this data throughout the semester on your lab assignments. Student # Gender Height Shoe Age Hand 1 F 68 8.5 20 R 2 F 60 5.5 27 R 3 F 64 7 31 R 4 F 67 7.5 19 R 5 F 65 8 20...
A data processing company has a training program for new salespeople. After completing the training program,...
A data processing company has a training program for new salespeople. After completing the training program, each trainee is ranked by his or her instructor. After a year of sales, the same class of trainees is again ranked by a company supervisor according to net value of the contracts they have acquired for the company. The results for a random sample of 11 salespeople trained in the last year follow, where x is rank in training class and y is...
A data processing company has a training program for new salespeople. After completing the training program,...
A data processing company has a training program for new salespeople. After completing the training program, each trainee is ranked by his or her instructor. After a year of sales, the same class of trainees is again ranked by a company supervisor according to net value of the contracts they have acquired for the company. The results for a random sample of 11 salespeople trained in the last year follow, where x is rank in training class and y is...
Use the given data to find the equation of the regression line. Examine the scatterplot and...
Use the given data to find the equation of the regression line. Examine the scatterplot and identify a characteristic of the data that is ignored by the regression line. x 9 8 11 9 10 13 6 4 12 7 5 y 7.24 7.08 13.32 7.2 7.47 8.99 5.84 5.23 8.55 6.61 5.62 Create a scatterplot of the data. Choose the correct graph below. A. 0510152025 0510152025xy A scatterplot has a horizontal x-scale from 0 to 25 in intervals of...