Additional Practice Problems 1
These practice problems are for those wanting additional
practice calculating statistics using Microsoft (TM) Excel.
They should be done after the problems in the file Quick
Statistics Using Microsoft™ Excel.
Problems, data sets, and key answers are provided. Directions
on how to do the problems are not provided. For those wanting
assistance, a link to Quick Statistics Using Microsoft (TM) Excel
(ISBN 192985000X) has been provided.
Problems, with slight modifications, and data sets have been
taken from the Quick Questions of Statistics (ISBN 0963277251)
of The Quick Notes Learning System (TM) series. Names of
Chapters and their numbers are from said book.
Additional Practice Problems are located on 6 sheets.
Sheet 1 Descriptive Statistics
Sheet 2 Probability, The Basis for Inferential Statistics
Sheet 3 Inferential Statistics/Large Sample Hypothesis Testing
Sheet 4 Inferential Statistics/Small Sample Hypothesis Testing Using Student's t test
Sheet 5 Analysis of Variance and Chi-square
Sheet 6 Correlation and Regression
Copyright laws prohibit the reproduction and transmission of this
material or any portion of this material in any form and by any means
without written permission of 21st Century Learning Products.
Disks may be copied for educational purposes for a reasonable fee.
Contact Walter Antoniotti at 1-800-253-6595.
Sheet 1 Descriptive Statistics
Directions are in Answers 3 of Quick Statistics Using Microsoft (TM) Excel.
Chapter 2 Summarizing Data
1A) Make an array and calculate a range for this data. Data Do your calculations in
38 columns D40 and C43-C47.
1B) Using this data, make a 5 class Frequency Distribution, Histogram, 48 Range
and Cumulative Relative Frequency Distribution with the first class 27
having class limits of 5 - 14 and the remaining classes of equal width. 14 bins
31
23
46
38
54
26 Begin your graph in C49.
44
33
17
34
6
37
Answers: Range is 54 - 6 = 48 and bins are 14, 24, 34, 44, and 54.
Chapter 3 Measuring Central Tendency of Ungrouped Data
2) Use fx to calculate the sample mean, median, mode, 1st quartile, Data Mean
3rd quartile, interquartile range, second decile, and 85th percentile 5
for this data. 7 Median
3
8 Mode
6
10 Q1
9
8 Q3
Q1 to Q3
D2
P85
Answers: Mean 7, Median 7.5, Mode 8, Q3 - Q1 = 8.25 - 5.75 = 2.5,
2nd decile = 5.4 and 85th percentile = 8.95. Measure of position
answers differ from those in Statistics (ISBN 0963277251) from
The Quick Notes Learning System because of different assumptions
of the location of numbers along a number line.
Chapter 4 Measuring Dispersion of Ungrouped Data
3) Use fx to calculate the average deviation, standard deviation, and Do your calculations
variance for this sample data. Data in column D.
5 Ad
7
3 SD
8
6 V
10
9
8
Answers: AD 1.8, SD 2.3, V 5.1
The problems of Chapters 5 - 8 of Statistics (ISBN0963277251)
of The Quick Note Learning System will not be done with Excel.
Sheet 2 on Probability, The Basis for Inferential Statistics
Directions are in Answers 5 of Quick Statistics Using Microsoft (TM) Excel
Chapter 9 Discrete Probability Distributions
1) Five percent of the parts coming off an assembly line are defective. Do your calculations in column C.
Assuming a binomial probability distribution, calculate the probability
of exactly 2 out of 5 parts being defective. Determine the distribution P(x = 2) =
of defective parts.
x
0
1
2
3
4
5
Quick's Answers:
P(x = 2) = 0.0214344
x P(x)
0 0.7737809
1 0.2036266
2 0.0214344
3 0.0011281
4 2.969E-05
5 3.125E-07
2) A bank found that the average number of cars waiting during the x
noon hour at a drive-up window follows a Poisson distribution with 0
a mean of 2 cars. Make a chart of this distribution and answer 1
these questions concerning the probability of cars waiting at the 2
drive-up window. 3
4
Place Your Answers Here 5
A) P(x=0) = 6
B) P(x=2) = 7
C) P(x>=3) = 8
D) P(x=3) = 1 - 0.676676 = 0.323324 4 0.0902235 0.947347
D) P(x$38,000) Income
Mean 30,000
SD 4,000
Z
(P$38,000) =
C) P($18,000$30,000) Income
Mean 30,000
SD 4,000
Z
(P$30,000) =
Quick's Answers:
A) P(x$38,000) =1-.4772= 0.0228
C) P($18,000$30,000) = 0.50
4) Grades of State University graduates are normally distributed with
a mean of 3.0 and a standard deviation of .3. Calculate the following:
A. What grade point average is required to be in the top 5% of the z
graduating class? St Dev 0.3
Mean 3
95th %
B. Calculate the interquartile range. z z
St Dev 0.3 St Dev 0.3
Mean 3 Mean 3
Q1 Q3
C. An eccentric alumnus left scholarship money for students in the z z
third decile from the bottom of their class. Determine the range of St Dev 0.3 St Dev 0.3
the third decile. Would a student with a 2.8 grade point average Mean 3 Mean 3
qualify for this scholarship? LL UL
Quick's Answers:
A) >3.49
B) 2.80 to 3.20
C) 2.75 to 2.84, yes
Chapter 11 Sampling and the Sampling Distribution of the Means
Directions are in A96 of Answers 5 Data
55
5.Calculate the 95% and 99% confidence intervals for the population 58
mean given a sample of 36 resulted in a mean of 55 and a rounded 52
standard deviation of 18. 72
Answer using problem's rounded standard deviation. HW,95% 55
LL 50
UL 47
98
39
HW,99% 57
LL 62
UL 53
50
52
Quick's Answer using problem's rounded standard deviation. HW,95% 5.879892 25
LL 49.120108 35
UL 60.879892 81
75
54
HW,99% 7.7274879 56
LL 47.272512 48
UL 62.727488 55
71
Your Answer using actual standard deviation. 55
43
85
32
21
85
55
LL 73
UL 26
38
81
40
46
LL
UL
Quick's Answer using actual standard deviation. Data
HW, 95% 6.0903312
LL 48.909669
UL 61.090331
Data
HW,99% 8.1714279
LL 46.828572
UL 63.171428
Sheet 3 Inferential Statistics/Large Sample Hypothesis Testing
Note: Do sheet 3-6 problems with the actual data and not the problem's
rounded statistics.
Chapter 13 Large Sample Hypothesis Testing
Directions are in Answers 7 of Quick Statistics Using Microsoft (TM) Excel Pr. 1
Life
1) A light bulb warranty states average bulb life is at least Do calculations (ooo)
20,000 hours. A sample of 49 bulbs had an average life of in column C 19
19,000 hours. The population standard deviation is 1,400 hours. 17
Test the warranty claim to the .01 level of significance. hypothesized 18
population mean 19
19
alpha 20
19
sample mean 21
20
sample standard 22
deviation 20
Your Analysis: 19
count 19
21
st error of mean 19
19
z from data 18
19
critical value for z 17
19
Quick's Calculations: hypothesized 19
population mean 20 19
19
alpha 0.01 16
20
sample mean 19 19
20
sample standard 1.3994 17
deviation 19
18
count 49 18
18
st error of mean 0.1999 21
17
z from data -5.0021 18
Quick's Analysis: -5.0021 is beyond -2.33, the critical value of z for 20
an alpha of .01, the null hypothesis is rejected. The claim is not critical value for z 2.3263 21
substantiated. 18
16
21
2) Average weekly manufacturing earnings were $480 and the Do calculations 17
standard deviation was $72. A recent sample of 36 resulted in column C. 19
in a mean of $450. The standard deviation has not changed. 20
Test to the .05 level whether average weekly earnings changed. hypothesized 22
population mean 19
20
18
alpha 20
18
sample mean
Your Analysis: sample standard
deviation
count
st error of mean
z from data
critical value for z
Quick's Calculations: hypothesized 480
population mean
alpha 0.05
sample mean 450
sample standard 71.992
deviation
count 36
st error of mean 11.999
Quick's Analysis: -2.5 is beyond -1.96, the critical value of alpha z from data -2.5003
for .01, the null hypothesis is rejected. Weekly earnings changed.
critical value for z 1.96
Chapter 14 Large Sample Hypothesis Testing Part II Place the results
of your test in B98.
3) Ace Realty wants to determine whether the average time it
takes to sell homes is different for its two offices. A sample of
40 sales from office #1 revealed a mean of 90 days and a standard
deviation of 15 days. A sample of 50 sales from office #2 revealed
a mean of 100 days and a standard deviation of 20 days. Use a
.05 level of significance.
Your Analysis:
Quick's Analysis: z of -2.71 is beyond the critical value of 1.96 z-Test: Two Sample for Means
for this two-tail problem. Reject Ho, sales time is not the
same at these two offices. O1 O2
Mean 90 100
Note: The p-value of 0.0067 is below the alpha of .05 Known Variance 225 400
and this indicates a small tail and a big difference so Observations 40 50
again we reject the null hypothesis. 0
Hypothesized Mean Difference
z -2.7091
P(ZB Total
Math Courses Taken
2 5 25 30
Total 20 30 50
Expected Outcomes
Grades B
Your Answer: Math Courses Taken
2
p-value =
Quick's Answers:
Observed Outcomes
Grades B Total
Math Courses Taken
2 5 25 30
Total 20 30 50
Results: The answer of a p-value of 0.000037 is smaller than .01 Expected Outcomes
indicating grades higher than B in statistics and number of math Grades B
courses taken are dependent (not independent). Math Courses Taken
2 12 18
p-value= 3.71074E-05 = 0.000037
Sheet 6 Correlation and Regression
Directions are in Answers 11 of Quick Statistics Using Microsoft (TM) Excel.
Chapter 23 and 24 on Correlation
and Simple Linear Regression Analysis
Hours Grades
1) Determine the following for this data on hours studying 3 3.0
per weekend and grade point average. 2 2.0
6 3.8
1) scatter diagram with hours the independent variable 3 2.6
2) coefficient of correlation 4 3.2
3) coefficient of determination 8 3.7
4) coefficient of nondetermination 2 2.1
5) average error for predicting grades 3 2.8
6) .01 level of significance test for slope being 0
7) regression equation
8) expected grades for people who study 5 hours Place your graph in B19.
Place the beginning of your
Your Analysis: calculations in B38.
Expected Grades =
Quick's Scatter Diagram
Correlation of Grades and Study
Hours
4.5
3.5
Grades
2.5
1.5
0 2 4 6 8 10
Study Hours
Quick's Analysis
Multiple R, the coefficient of correlation, of 0.889 is high.
R-Squared, 0.791 is the coefficient of determination.
It shows that 79.1% of the variability of grades is SUMMARY OUTPUT
accounted for by study hours variability.
Coefficient of nondetermination, 1 - R-Squared, is Regression Statistics
0.209. It shows that 20.9% of the variability in Multiple R 0.889273
grades is not accounted for by study hours variability. R Square 0.790806
Standard error of 0.33 indicates the average error Adjusted R Square 0.75594
in predicting grades is 0.33 points. Standard Error 0.328761
Anova p-value of 0.003 is below the .01 level of Observations 8
significance so we reject the null hypothesis that
the slope could be zero. ANOVA
Intercept coefficient of 1.808097 is the y-intercept. df SS MS
Hours coefficient of 0.281781 is the slope. Regression 1 2.4515 2.4515
Regression equation rounded is Y.x = 1.81 + 0.28x. Residual 6 0.6485 0.1081
The expected grades for people who study 5 hours Total 7 3.1
is solved by inserting =C97+(C98*5) into cell B100.
Coefficients Stand Err t Stat
Intercept 1.808097 0.2571 7.034
Hours 0.281781 0.0592 4.7625
Expected Grades = 3.217004049
10
F Significance F
22.68148333 0.0031183
P-value Lower 95% Upper 95%
0.000412494 1.1791145 2.43708
0.003118337 0.1370058 0.426557