Need to use excel sheet to complete the topic

HHSIZE
FOOD
2
3
3
6
4
2
1
5
6
4
4
7
3
5
6
2
4
4
3
3
2
2
4
1
4
6
11
3
4
3
2
2
2
4
6
2
10
3
6
5
1.04
3.68
4.43
2.98
6.3
1.46
8.82
9.62
4.41
2.73
9.31
3.59
5.36
1.47
7.06
2.52
1.75
5.64
1.93
6.46
6.72
5.76
9.72
0.16
5.52
11.92
4.68
4.76
7.85
2.9
2.87
5.09
3.17
2.4
13.2
2.07
2.94
1.42
10.44
3
TOTAL
10.76
19.96
27.6
38.11
27.9
21.9
21.83
49.27
33.27
35.54
44.44
45.17
33.07
10.35
44.44
24.13
37.6
38.96
17.17
31.6
23.11
27.56
30.96
4.08
23.22
42.99
51.9
20.2
36.8
19.02
15.43
20.71
17.01
45.35
49.37
18.06
54.47
13.12
45.77
34.79
CANADA penny
weight (g)
2,25
2,28
2,29
2,29
2,32
2,27
2,29
2,40
2,33
2,40
2,31
2,33
2,37
2,37
2,29
2,26
2,29
2,28
2,40
2,25
2,28
2,28
2,39
2,34
2,32
2,26
2,32
2,31
2,26
2,26
2,32
2,29
2,35
2,37
2,27
2,25
2,34
2,26
2,33
2,40
2,29
2,35
2,25
2,37
2,33
USA penny weight
(g)
2,47
2,44
2,46
2,44
2,46
2,42
2,51
2,50
2,43
2,47
2,45
2,42
2,41
2,47
2,46
2,44
2,43
2,46
2,49
2,47
2,52
2,52
2,51
2,42
2,43
2,47
2,40
2,40
2,44
2,46
2,40
2,48
2,50
2,51
2,41
2,44
2,43
2,41
2,42
2,47
2,45
2,44
2,42
2,42
2,42
2,34
2,39
2,39
2,30
2,35
2,41
2,46
2,49
2,51
2,48
STAT 1P98
F2022
STAT 1P98 – Written Assignment #3
Due Date: Monday, November 28th, 2022 @ 4:30 pm
The written assignment will be submitted digitally to Crowdmark. Use .pdf, jpg or png format for
your assignment submissions.
➢ An email will be sent to your account to access Crowdmark
➢ Login with your student account
➢ For each question submit an image or pdf of your solution. Each question is to be
uploaded separately and each will accept multiple files.
➢ When the assignment is completed, press Submit at the end of the page
➢ You can edit and change your answers up until the due date
➢ Please do not wait until last minute to submit your assignment as last-minute unforeseen
events do occur.
➢ Do not round any number during calculating, always use the exact number till you get the
final answer
Excel Hint for finding critical values:
•
•
Two tailed t test= t. inv. 2t(α, df)
Left tailed t test = t. inv(α, df) or right tailed t test = t. inv(1 − α, df)
1. A previous study by Public Health Ontario found that at least 40.5% of teens have texted
while driving. In a recent survey of 821 random sample of teen drivers, 311 have admitted
that they have texted while driving. Use a 0.05 significance level to test the claim that the
texting-while-driving rate has decreased since the previous study, and it is now less than
40.5%.
a) State the null and alternative hypotheses.
b) Calculate the test statistic (round it to 3 decimal places).
c) Find the critical value (round it to 3 decimal places).
d) To apply normal approximation to the distribution of sample proportion, show that
whether all 3 of the requirements of the normal approximation are satisfied.
e) Make a conclusion based on your answer from part b) and c). Is there sufficient
evidence that the texting-while-driving rate has decreased?
2. We will compare the weight of a penny (1¢) from Canada to the weight of a penny from
the United States. A random sample of 50 pennies each has been collected from Canada
and the United States. Download the “Weight of Pennies” excel file for the data set. To
compare the mean weight of pennies from Canada and from the United States,
a) Calculate the sample mean and the sample standard deviation of the weight of
pennies from Canada and the United States respectively (round to 3 decimal places
when applicable).
STAT 1P98
F2022
b) Test the claim that the pennies from Canada have the same mean weight of the
pennies from the United States, state the null and alternative hypotheses
c) If the population standard deviation 𝜎1 and 𝜎2 are unknown (assuming unequal),
Calculate the test statistics using formula (round to 3 decimal places).
d) Use Excel Data Analysis function, at 0.05 significance level, compare two sample
mean with unequal variance, submit a screenshot of the result output. (t Stat from
the output should match your answer in part c).
e) Based on your answer from c) or d), at 𝛼 = 0.05 level, what is your conclusion?
3. The data in the table below were obtained through a survey of randomly selected
individuals in Ontario
Speeding Tickets Received Within the Last Year?
Yes
No
Male
28
222
Female
25
471
a) Find the probability 𝑝1 of getting someone ticketed for speeding, given that the
selected person is a man (round to 3 decimal places).
b) Find the probability 𝑝2 of getting someone ticketed for speeding, given that the
selected person is a woman (round to 3 decimal places).
c) Use a 0.01 level of significance to test the claim that men are more likely to receive
a speeding ticket than women.
• To use normal approximation, check the sample size requirements
• Calculate the test statistics (round to 3 decimal places).
d) Find the critical value (round it to 3 decimal places).
e) Make a conclusion based on your answer from part c) and d).
4. 11 Healthy adults’ initial temperature (Temp 1) have been measured while sitting still, then
their temperatures are measured again after laying down (Temp 2).
Subject
Temp 1
Temp 2
1
2
3
4
5
6
7
8
9
10
11
37.61 37.07 37.20 37.23 37.38 37.59 37.28 37.12 37.55 37.04 37.78
37.19 36.91 36.70 36.80 36.96 37.25 37.11 36.82 37.36 36.92 37.22
a) To test the claim that the mean temperature difference is 0 while sitting still vs.
laying down, state the null and alternative hypotheses.
b) At 𝛼 = 0.01 significance level, find the critical value (round to 3 decimal places).
c) Calculate the test statistics (round to 3 decimal places).
STAT 1P98
F2022
d) Use Excel Data Analysis function, at 0.01 significance level, compare the two
paired sample mean, submit a screenshot of the result output. (t Stat from the
output should match your answer in part c).
e) Based on your answer from c) or d), at 𝛼 = 0.01 level, what is your conclusion?
5. Download the “Weekly Garbage” excel file for the data set. Data provided by Masakazu
Tani, the Garbage Project, University of Arizona. The HHSIZE column is the household
size, the FOOD column is the weight of food discarded in pounds, and the TOTAL column
is the total weight of all discarded materials in pounds.
a) Use excel Data Analysis, correlation function, to find the correlation between
household size and the weight of food discarded. Submit a screenshot of the result
output.
i. When selecting the input range, make sure to include the labels in first row
ii. Make sure to check the “labels in first row” box so that your result output
has proper labels.
b) While the household size and weight of food discarded columns are selected, use
the insert tab, to create a scatter plot from the charts section.
i. Edit the chart title so that it reads “HHSIZE and FOOD scatter plot created
by Xiang Raney-Yan” (replace Xiang Raney-Yan with your name), Submit
a screenshot of the scatterplot.
ii. Based on the r value from a) and the scatter plot from b), what can you
conclude about the correlation between household size and food discarded?
c) Repeat part a), but this time, find the correlation between household size and the
total weight of all discarded material. Submit a screenshot of the result output.
Hint: You may need rearrange the order of the columns.
d) Repeat part b), but this time, plot the scatter plot for household size and the total
weight of all discarded material. Submit a screenshot of the plot with proper title.
What can you conclude about the correlation?
e) To test the significance of correlation between household size and the total weight
of all discarded material, test the hypothesis 𝜌 = 0 vs 𝜌 > 0 at 𝛼 = 0.05 level.
i. Calculate the t statistics using formula, with 𝑟 value calculated in part c)
(round to 3 decimal places).
ii. Use Excel to find the critical value (round to 3 decimal places) and state the
degree of freedom.
iii. Conclusion on the significance of the correlation?
STAT 1P98
F2022
6. With the same “Weekly Garbage” Excel data set. Use household size as the predictor
variable – 𝑥, use the total weight of all discarded material as the response variable – 𝑦. To
predict the total weight of all material discarded weekly by a random selected household
given the household size:
a) Use Excel Data Analysis, regression function, to get the summary output and plot.
i. With HHSIZE and TOTAL variables in column A and B, select output
range in cell E2
ii. Make sure variable labels are also selected and check the “labels” box.
iii. Make sure the “Line Fit Plots” box is checked. Then click on “OK”
iv. Edit the line fit plot title so that it reads “Created by Xiang Raney-Yan”
(replace Xiang Raney-Yan with your name).
v. While the line fit plot is selected, go to “Chart Design” tab, under “Quick
Layout”, choose the layout that will display the best fitting line, the
regression expression and the 𝑅 2 value. (Depends on your excel version,
most likely it is layout #9)
vi. Submit a screenshot of the edited Line Fit Plot
b) On your summary output
i. Highlight multiple R value.
This value should match your R answer in Q5 c), since our regression only
have one predictor.
ii. Highlight the t statistics for HHSIZE
This value should match your answer in Q5 e), the significance of the
predictor (or regressor or factor)
iii. Highlight the F test statistics for the regression
This is the test statistics for the significance of the regression
iv. Submit a screenshot of the summary output with the 3 numbers
highlighted.
c) What is the relationship between the 𝑡 test statistics and 𝐹 test statistics highlighted
in part b) and explain why? (Hint, calculate 𝑡 2 )
d) Predict the total weight of all material discarded in a week, given a random
household of size 12. (Round to 2 decimal places).