Excel 2019 in Applied Statistics for High School Students A Guide to Solving Practical Problems

255 Pages·2021·11.433 MB·English

by  Thomas J. Quirk

Excel for Statistics
Thomas J. Quirk
Excel 2019 in
Applied Statistics
for High School
Students
A Guide to Solving Practical Problems
Second Edition
Excel for Statistics
Excel for Statistics is a series of textbooks that explain how to use Excel to solve
statisticsproblemsinvariousfieldsofstudy.Professors,students,andpractitioners
will find these books teach how to make Excel work best in their respective field.
Applications include any disciplines that use data and can benefit from the power
andsimplicityofExcel.Bookscoverallthestepsforrunningstatisticalanalysesin
Excel2019,Excel2016andExcel2013.Theapproachalsoteachescriticalstatistics
skills,makingthebooksparticularlyapplicableforstatisticscoursestaughtoutside
ofmathematicsorstatisticsdepartments.
Serieseditor:ThomasJ.Quirk
Thefollowingbooksareinthisseries:
T.J. Quirk, Excel 2019 in Applied Statistics for High School Students: A Guide to Solving Practical
Problems,ExcelforStatistics.SpringerInternationalPublishingAG,partofSpringerNature2021.
T.J.Quirk,S.Cummings,Excel2019forSocialWorkStatistics:AGuidetoSolvingPracticalProblems.
Excelforstatistics.SpringerInternationalPublishingAG,partofSpringerNature2021
T.J.Quirk,M.Quirk,H.F.Horton,Excel2019forEnvironmentalSciencesStatistics:AGuidetoSolving
PracticalProblems,ExcelforStatistics.SpringerInternationalPublishingAG,partofSpringerNature
2021.
T.J.Quirk,S.Cummings,Excel2019forHealthServicesManagementStatistics:AGuidetoSolving
PracticalProblems.Excelforstatistics.SpringerInternationalPublishingAG,partofSpringerNature
2020.
T.J. Quirk, J. Palmer-Schuyler, Excel 2019 for Human Resource Management Statistics: A Guide to
SolvingPracticalProblems,ExcelforStatistics.SpringerInternationalPublishingAG,partofSpringer
Nature2020.
T.J. Quirk, M. Quirk, H.F. Horton, Excel 2019 for Physical Sciences Statistics: A Guide to Solving
PracticalProblems,ExcelforStatistics.SpringerInternationalPublishingAG,partofSpringerNature
2021.
T.J.Quirk,E.Rhiney,Excel2019forAdvertisingStatistics:AGuidetoSolvingPracticalProblems,Excel
forStatistics.SpringerInternationalPublishingSwitzerland2020.
T.J.Quirk,E.Rhiney,Excel2019forMarketingStatistics:AGuidetoSolvingPracticalProblems,Excel
forStatistics.SpringerInternationalPublishingSwitzerland2021.
T.J.Quirk,M.Quirk,H.F.Horton,Excel2019forBiologicalandLifeSciencesStatistics:AGuideto
SolvingPracticalProblems,ExcelforStatistics.SpringerInternationalPublishingAG,partofSpringer
Nature2020.
T.J.Quirk,Excel2019forBusinessStatistics:AGuidetoSolvingPracticalProblems,ExcelforStatistics.
SpringerInternationalPublishingAG,partofSpringerNature2020.
T.J. Quirk, Excel 2019 for Engineering Statistics: A Guide to Solving Practical Problems, Excel for
Statistics.SpringerInternationalPublishingAG,partofSpringerNature2020.
T.J. Quirk, Excel 2019 for Educational and Psychological Statistics: A Guide to Solving Practical
Problems,ExcelforStatistics.SpringerInternationalPublishingAG,partofSpringerNature2020.
T.J.Quirk,Excel2019forSocialScienceStatistics:AGuidetoSolvingPracticalProblems,Excelfor
Statistics.SpringerInternationalPublishingAG,partofSpringerNature2021.
T.J. Quirk, Excel 2016 Applied Statistics for High School Students: A Guide to Solving Practical
Problems,ExcelforStatistics.SpringerinternationalPublishingSwitzerland2018.
T.J.Quirk,E.Rhiney,Excel2016forAdvertisingStatistics:AGuidetoSolvingPracticalProblems,Excel
forStatistics.SpringerInternationalPublishingSwitzerland2017.
T.J.Quirk,S.Cummings,Excel2016forSocialWorkStatistics:AGuidetoSolvingPracticalProblems.
Excelforstatistics.SpringerInternationalPublishingSwitzerland2017.
T.J.Quirk,E.Rhiney,Excel2016forMarketingStatistics:AGuidetoSolvingPracticalProblems,Excel
forStatistics.SpringerInternationalPublishingSwitzerland2016.
T.J.Quirk,Excel2016forBusinessStatistics:AGuidetoSolvingPracticalProblems,ExcelforStatistics.
SpringerInternationalPublishingSwitzerland2016.
T.J. Quirk. Excel 2016 for Engineering Statistics: A Guide to Solving Practical Problems, Excel for
Statistics.SpringerInternationalPublishingSwitzerland2016.
T.J.Quirk,M.Quirk,H.F.Horton,Excel2016forBiologicalandLifeSciencesStatistics:AGuideto
SolvingPracticalProblems,ExcelforStatistics.SpringerInternationalPublishingSwitzerland2016.
T.J. Quirk. Excel 2016 for Educational and Psychological Statistics: A Guide to Solving Practical
Problems,ExcelforStatistics.SpringerInternationalPublishingSwitzerland2016.
T.J.Quirk,Excel2016forSocialScienceStatistics:AGuidetoSolvingPracticalProblems,Excelfor
Statistics.SpringerInternationalPublishingSwitzerland2016.
T.J.Quirk,M.Quirk,H.Horton,Excel2016forPhysicalSciencesStatistics:AGuidetoSolvingPractical
Problems.ExcelforStatistics.SpringerInternationalPublishingSwitzerland2016.
T.J.Quirk,S.Cummings,Excel2016forHealthServicesManagementStatistics:AGuidetoSolving
PracticalProblems.ExcelforStatistics.SpringerInternationalPublishingSwitzerland2016.
T.J. Quirk, J. Palmer-Schuyler, Excel 2016 for Human Resource Management Statistics: A Guide to
SolvingPracticalProblems,ExcelforStatistics.SpringerInternationalPublishingSwitzerland2016.
T.J.Quirk,M.Quirk,H.F.Horton.Excel2016forEnvironmentalSciencesStatistics:AGuidetoSolving
PracticalProblems,ExcelforStatistics.SpringerInternationalPublishingSwitzerland2016.
T.J. Quirk, M. Quirk, H.F. Horton. Excel 2013 for Physical Sciences Statistics: A Guide to Solving
PracticalProblems,ExcelforStatistics.SpringerInternationalPublishingSwitzerland2016.
T.J.Quirk,S.Cummings,Excel2013forHealthServicesManagementStatistics:AGuidetoSolving
PracticalProblems.ExcelforStatistics.SpringerInternationalPublishingSwitzerland2016.
T.J. Quirk, J. Palmer-Schuyler, Excel 2013 for Human Resource Management Statistics: A Guide to
SolvingPracticalProblems,ExcelforStatistics.SpringerInternationalPublishingSwitzerland2016.
T.J.Quirk,Excel2013forBusinessStatistics:AGuidetoSolvingPracticalProblems,ExcelforStatistics.
SpringerInternationalPublishingSwitzerland2015.
T.J. Quirk. Excel 2013 for Engineering Statistics: A Guide to Solving Practical Problems, Excel for
Statistics.SpringerInternationalPublishingSwitzerland2015.
T.J.Quirk,M.Quirk,H.F.Horton,Excel2013forBiologicalandLifeSciencesStatistics:AGuideto
SolvingPracticalProblems,ExcelforStatistics.SpringerInternationalPublishingSwitzerland2015.
T.J. Quirk. Excel 2013 for Educational and Psychological Statistics: A Guide to Solving Practical
Problems,ExcelforStatistics.SpringerInternationalPublishingSwitzerland2015.
T.J.Quirk,Excel2013forSocialScienceStatistics:AGuidetoSolvingPracticalProblems,Excelfor
Statistics.SpringerInternationalPublishingSwitzerland2015.
T.J.Quirk,M.Quirk,H.F.Horton,Excel2013forEnvironmentalSciencesStatistics:AGuidetoSolving
PracticalProblems,ExcelforStatistics.SpringerInternationalPublishingSwitzerland2015.
T.J.Quirk,M.Quirk,H.F.Horton,Excel2010forEnvironmentalSciencesStatistics:AGuidetoSolving
PracticalProblems,ExcelforStatistics.SpringerInternationalPublishingSwitzerland2015.
T.J. Quirk, J. Palmer-Schuyler, Excel 2010 for Human Resource Management Statistics: A Guide to
SolvingPracticalProblems,ExcelforStatistics.SpringerInternationalPublishingSwitzerland2014.
AdditionalStatisticsbooksbyDr.TomQuirkthathavebeenpublishedbySpringer
T.J.Quirk,Excel2010forBusinessStatistics:AGuidetoSolvingPracticalProblems.SpringerScience+
BusinessMedia2011.
T.J. Quirk. Excel 2010 for Engineering Statistics: A Guide to Solving Practical Problems. Springer
InternationalPublishingSwitzerland2014.
T.J.Quirk,S.Cummings,Excel2010forHealthServicesManagementStatistics:AGuidetoSolving
PracticalProblems.SpringerInternationalPublishingSwitzerland2014.
T.J.Quirk,M.Quirk,H.Horton,Excel2010forPhysicalSciencesStatistics:AGuidetoSolvingPractical
Problems.SpringerInternationalPublishingSwitzerland2013.
T.J.Quirk,M.Quirk,H.F.Horton,Excel2010forBiologicalandLifeSciencesStatistics:AGuideto
SolvingPracticalProblems.SpringerScience+BusinessMediaNewYork2013.
T.J.Quirk,Excel2010forSocialScienceStatistics:AGuidetoSolvingPracticalProblems.Springer
Science+BusinessMediaNewYork2012.
T.J. Quirk, Excel 2010 for Educational and Psychological Statistics: A Guide to Solving Practical
Problems.SpringerScience+BusinessMediaNewYork2012.
T.J.Quirk,Excel2007forBusinessStatistics:AGuidetoSolvingPracticalProblems.SpringerScience+
BusinessMediaNewYork2012.
T.J. Quirk, Excel 2007 for Educational and Psychological Statistics: A Guide to Solving Practical
Problems.SpringerScience+BusinessMediaNewYork2012.
T.J.Quirk,Excel2007forSocialScienceStatistics:AGuidetoSolvingPracticalProblems.Springer
Science+BusinessMediaNewYork2012.
T.J.Quirk,Excel2007forBiologicalandLifeSciencesStatistics:AGuidetoSolvingPracticalProblems.
SpringerScience+BusinessMediaNewYork2013.
Moreinformationaboutthisseriesathttp://www.springer.com/series/13491
Thomas J. Quirk
Excel 2019 in Applied
Statistics for High School
Students
A Guide to Solving Practical Problems
Second Edition
ThomasJ.Quirk
ProfessorEmeritusofMarketing,WebsterUniversity
St.Louis,MO,USA
ISSN2570-4605 ISSN2570-4613 (electronic)
ExcelforStatistics
ISBN978-3-030-66755-9 ISBN978-3-030-66756-6 (eBook)
https://doi.org/10.1007/978-3-030-66756-6
©SpringerNatureSwitzerlandAG2021
Thisworkissubjecttocopyright.AllrightsarereservedbythePublisher,whetherthewholeorpartofthe
materialisconcerned,specificallytherightsoftranslation,reprinting,reuseofillustrations,recitation,
broadcasting,reproductiononmicrofilmsorinanyotherphysicalway,andtransmissionorinformation
storageandretrieval,electronicadaptation,computersoftware,orbysimilarordissimilarmethodology
nowknownorhereafterdeveloped.
Theuseofgeneraldescriptivenames,registerednames,trademarks,servicemarks,etc.inthispublication
doesnotimply,evenintheabsenceofaspecificstatement,thatsuchnamesareexemptfromtherelevant
protectivelawsandregulationsandthereforefreeforgeneraluse.
The publisher, the authors, and the editorsare safeto assume that the adviceand informationin this
bookarebelievedtobetrueandaccurateatthedateofpublication.Neitherthepublishernortheauthorsor
theeditorsgiveawarranty,expressedorimplied,withrespecttothematerialcontainedhereinorforany
errorsoromissionsthatmayhavebeenmade.Thepublisherremainsneutralwithregardtojurisdictional
claimsinpublishedmapsandinstitutionalaffiliations.
ThisSpringerimprintispublishedbytheregisteredcompanySpringerNatureSwitzerlandAG
Theregisteredcompanyaddressis:Gewerbestrasse11,6330Cham,Switzerland
This book is dedicated to the more than 3,000
studentsIhavetaughtatWebsterUniversity’s
campuses in St. Louis, London, and Vienna;
the students at Principia College in Elsah,
Illinois; and the students at the Cooperative
State University of Baden-Wuerttemberg in
Heidenheim, Germany. These students taught
me a great deal about the art of teaching.
I salutethem all, andI thankthem forhelping
me to become a better teacher.
Thomas J. Quirk
Preface
Excel 2019 in Applied Statistics for High School Students: A Guide to Solving
Practical Problems updates the Excel steps and screenshots from the previously
published Excel 2016 in Applied Statistics for High School Students: A Guide to
Solving Practical Problems, and it contains a number of important changes. The
explanationsofstatisticsandstatisticalformulashavebeenmadeclearer.TheExcel
steps now match perfectly the Excel 2019 version. Thirty percent of the end-of-
chapter problems, and their answers in an Appendix, are new to this book. Thirty
percentofthe160+screenshotsarenewsothattheymatchthenewExcelcommands
toensurethatyouareusingExcelcorrectlyeachstepoftheway.
Theeightchaptersinthebook(Mean,StandardDeviation,andStandardErrorof
the Mean; Random Sampling; Confidence Interval about the Mean; One-Group
t-test; Two Group t-test; Correlation and Linear Regression; Multiple Correlation;
andOne-WayAnalysisofVariance)havebeenrewrittentoimprovetheirexplana-
tion of statistics. The answers to all of the problems in the book are provided, and
thereisaPractice Test sothat youcantest yourabilitytosolve statistics problems
usingExcel.Thisbookisanintroductiontostatistics,notafull-blownexplanationof
statistics.
Awordofcaution:Thisbookdoesnotattempttoteachyouallofthe“bellsand
whistles” of Excel 2019. We have left that objective to other books. Instead, this
bookwillteachyoutheExcelstepsyouneedtosolvetheinterestingproblemsinthe
book.YoushouldthinkofExcelasmerelythe“computerlanguage”neededtosolve
statisticsproblems.Inasense,thisapproachissimilartotheoneyouwouldneedif
youplannedtospendayearlivinginEuropeinVienna,Austria,whereyouneeded
tolearnsomebasicGerman (e.g., “Howmuchdoesthiscost?”“Where isthetrain
station?”“Pleasegivemethebillformydinner.””HowcanIgettotheairport?”),
butyoudonotneedtobecomefluentinthatlanguagetosurvive.Thisbookfocuses
on the Excel steps needed to solve the problems in the book. The task of showing
youhowtousethemanypowersofExcelarebeyondthescopeofthisbook.
ThisbookwaswrittenbyaProfessorwhowantedtorespondtothecomplaintsof
manystudentsabouttheirinabilitytounderstandtheirstatisticstextbookandabout
ix
x Preface
their inability to understand their professor’s explanation of theoretical statistics.
Thisbookisself-instructionalanddoesnotdependonaprofessor’sexplanationof
statistics.Thisbookwillteachyouthegeneralconceptsofstatisticswithoutburying
youindullstatisticaltheory.YouwilllearnwhyyouareperformingtheExcelsteps
throughtheobjectivesincludedinthechapters.Thestatisticalconceptsandpractice
problems get progressively more sophisticated as they build on what you have
already learned from studying this book. This book is understandable by both
undergraduate and graduate students who are taking their first course in statistics,
byresearchers,andbyworkingprofessionalswhowanttosolveinterestingproblems
intheirchosenfieldofstudy.
This book was written by a Professor who is, first and foremost, committed to
helpingyoutounderstandhowtousestatisticstosolveinterestingproblemsinyour
chosenfieldofstudy.Theideasinthisbookhavebeenclassroomtestedoverthepast
11yearsinbothundergraduateandgraduatecoursesatWebsterUniversity,aliberal
arts college located in St. Louis, Missouri, in the middle of the USA. This book is
partofaseriesofmorethan30introductorystatisticstextbooks,in12fieldsofstudy,
thathavebeenpublishedbySpringerbyProf.Quirk,whichhavehelpedthousands
ofstudents,researchers,andworkingprofessionalslearnhowtouseExceltosolve
interestingstatisticsproblems.Thesefieldsofstudyinclude:(1)Business,(2)Edu-
cation/Psychology,(3)SocialScience,(4)BiologicalandLifeSciences,(5)Physical
Sciences, (6) Engineering, (7) Health Services Management, (8) Human Resource
Management, (9) Environmental Sciences, (10) Marketing, (11) Social Work, and
(12)Advertising.
At the beginning of his academic career, Prof. Tom Quirk spent 6 years in
educationalresearchattheAmericanInstitutesforResearchandEducationalTesting
Service. He then taught Social Psychology, Educational Psychology, General Psy-
chology, Accounting, Management, and Marketing at Principia College in Elsah,
Illinois,andiscurrentlyaProfessorEmeritusofMarketingintheWalkerSchoolof
Business&TechnologyatWebsterUniversitybasedinSt.Louis,Missouri(USA),
where he taught Marketing Statistics, Marketing Research, and Pricing Strategies.
He has published articles in the Journal of Educational Psychology, Journal of
Educational Research, Review of Educational Research, Journal of Educational
Measurement, and Educational Technology. In addition, he has published 20+
articlesinprofessionaljournalsandpresented20+papersatprofessionalmeetings,
including annual meetings of the American Educational Research Association, the
AmericanPsychologicalAssociation,andtheNationalCouncilonMeasurementin
Education.HeholdsaBSinMathematicsfromJohnCarrollUniversity,bothanMA
inEducationandaPhDinEducationalPsychologyfromStanfordUniversity,andan
MBAfromTheUniversityofMissouri-St.Louis.
St.Louis,MO,USA ThomasJ.Quirk
Acknowledgments
Excel 2019 in Applied Statistics for High School Students: A Guide to Solving
Practical Problems is the result of inspiration from three important people: my
two daughters and my wife. Jennifer Quirk McLaughlin invited me to visit her
MBA classes several times at the University of Witwatersrand in Johannesburg,
South Africa. These visits to a first-rate MBA program convinced me there was a
need for a book to teach students how to solve practical problems using Excel.
Meghan Quirk-Horton’s dogged dedication to learning the many statistical tech-
niques needed to complete her PhD dissertation illustrated the need for a statistics
book that would make this daunting task more user-friendly. And Lynne Buckley-
Quirk was the number-one cheerleader for this project from the beginning, always
encouragingmeandhelpingmeremaindedicatedtocompletingit.
ThomasJ.Quirk
xi