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

256 Pages·2018·12.23 MB·English

by  Thomas J. Quirk

Excel for Statistics
Thomas J. Quirk
Excel 2016 in
Applied Statistics
for High School
Students
A Guide to Solving Practical Problems
Excel for Statistics
Excel for Statistics is a series of textbooks that explain how to use Excel to solve
statisticsproblemsinvariousfieldsofstudy.Professors,students,andpractitioners
will findthese booksteach howtomake Excelworkbest intheirrespectivefields.
Applications include any discipline that uses data and can benefit from the power
andsimplicityofExcel.Bookscoverallthestepsforrunningstatisticalanalysesin
Excel 2016, Excel 2013, Excel 2010, and Excel 2007. The approach also teaches
criticalstatisticsskills,makingthebooksparticularlyapplicableforstatisticscourses
taughtoutsideofmathematicsorstatisticsdepartments.
Serieseditor:ThomasJ.Quirk
Thefollowingbooksareinthisseries:
T.J. Quirk, Excel 2016 in 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.
Moreinformationaboutthisseriesathttp://www.springer.com/series/13491
Thomas J. Quirk
Excel 2016 in Applied
Statistics for High School
Students
A Guide to Solving Practical Problems
ThomasJ.Quirk
WebsterUniversity
St.Louis,MO,USA
ExcelforStatistics
ISBN978-3-319-89992-3 ISBN978-3-319-89993-0 (eBook)
https://doi.org/10.1007/978-3-319-89993-0
LibraryofCongressControlNumber:2018940021
©SpringerInternationalPublishingAG,partofSpringerNature2018
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 editors are safe to assume that the advice and information in this
bookarebelievedtobetrueandaccurateatthedateofpublication.Neitherthepublishernortheauthorsor
theeditorsgiveawarranty,expressorimplied,withrespecttothematerialcontainedhereinorforany
errorsoromissionsthatmayhavebeenmade.Thepublisherremainsneutralwithregardtojurisdictional
claimsinpublishedmapsandinstitutionalaffiliations.
Printedonacid-freepaper
ThisSpringerimprintispublishedbytheregisteredcompanySpringerInternationalPublishingAGpartof
SpringerNature.
Theregisteredcompanyaddressis:Gewerbestrasse11,6330Cham,Switzerland
This book is dedicated to the more than three
thousand students I have taught at Webster
University’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-Wuerttemburg in Heidenheim,
Germany. These students taught me a great
deal about the art of teaching. I salute them
all, and I thank them for helping me to
become a better teacher.
Thomas J. Quirk
Preface
Excel 2016 in Applied Statistics for High School Students: A Guide to Solving
PracticalProblemshelpsanyonewhowantstolearnthebasicsofapplyingExcel’s
powerful statistical tools to their classes. If understanding statistics isn’t your
strongest suit, you are not mathematically inclined, or you are wary of computers,
thenthisisthebookforyou.
You’ll learn how to perform key statistical tests in Excel without being
overwhelmed by statistical theory. This book clearly and logically shows how to
runstatisticalteststosolvepracticalproblemsinseveralfieldsofstudy.
Excelisawidelyavailablecomputerprogramforstudentsandteachers.Itisalso
aneffectiveteachingandlearningtoolforquantitativeanalysesinstatisticscourses.
Its powerful computational ability and graphical functions make learning statistics
mucheasierthaninyearspast.However,thisisthefirstbooktoshowcaseExcel’s
usefulnessinteachingstatistics.Anditfocusesexclusivelyonthistopicinorderto
render the subject matter applicable and practical—and easy to comprehend and
apply.
Uniquefeaturesofthisbook:
(cid:129) Includes166colorscreenshotssoyoucanbesureyouareperformingExcelsteps
correctly.
(cid:129) Youwillbetoldeachstepoftheway,notonlyhowtouseExcel,butalsowhyyou
aredoingeachstep.
(cid:129) Includes specific objectives embedded in the text for each concept, so you can
knowthepurposeoftheExcelsteps.
(cid:129) YouwilllearnbothhowtowritestatisticalformulasusingExcelandhowtouse
Excel’sdrop-downmenusthatwillcreatetheformulasforyou.
(cid:129) Statistical theory and formulas are explained in clear language without bogging
youdowninmathematicalfinepoints.
(cid:129) Practicalexamplesofproblemsaretakenfromseveralfieldsofstudy.
vii
viii Preface
(cid:129) Each chapter presents key steps to solve practical problems using Excel. In
addition, three practice problems at the end of each chapter enable you to test
yournewknowledge.AnswerstotheseproblemsappearinAppendixA.
(cid:129) A“PracticeTest”isgiveninAppendixBtotestyourknowledgeattheendofthe
book.AnswerstothistestappearinAppendixC.
(cid:129) ThisbookdoesnotcomewithaCDofExcelfileswhichyoucanuploadtoyour
computer. Instead, you’ll be shown how to create each Excel file yourself. In a
classroom situation, your teachers will not give you an Excel file. You will be
expectedtocreateyourown.Thisbookwillgiveyouamplepracticeindevelop-
ingthisimportantskill.
(cid:129) This book is a tool that can be used either by itself or along with any good
statisticsbook.
Thisbookisappropriateforuseinanystatisticscourse—aswellasforteachers/
administratorswhowanttoimprovetheirExcelskills.
At the beginning of his academic career, Prof. Quirk spent six years in educa-
tional research at the American Institutes for Research and Educational Testing
Service. He then taught social psychology, educational psychology, general psy-
chology, accounting, management, and marketing at Principia College and is cur-
rentlyaProfessorofMarketingintheGeorgeHerbertWalkerSchoolofBusiness&
Technology at Webster University based in St. Louis, Missouri (USA), where he
teaches marketing statistics, marketing research, and pricing strategies. He has
publishedarticlesintheJournalofEducationalPsychology,JournalofEducational
Research, Review of Educational Research, Journal of Educational Measurement,
Educational Technology, The Elementary School Journal, Journal of Secondary
Education, Educational Horizons, and Phi Delta Kappan. In addition, he has
published 20+ articles inprofessionaljournals and presented20+ papers atprofes-
sional meetings, including annual meetings of the American Educational Research
Association,theAmericanPsychologicalAssociation,andtheNationalCouncilon
Measurement in Education. He holds a B.S. in mathematics from John Carroll
University,bothanM.A.ineducationandaPh.D.ineducationalpsychologyfrom
StanfordUniversity,andanM.B.A.fromtheUniversityofMissouri-St.Louis.
St.Louis,MO,USA ThomasJ.Quirk
Acknowledgments
Excel 2016 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
M.B.A. classes several times at the University of Witwatersrand in Johannesburg,
SouthAfrica.Thesevisitstoafirst-rateM.B.A.programconvincedmetherewasa
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-
niquesneededtocompleteherPh.D.dissertationillustratedtheneedforastatistics
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
ix
Contents
1 SampleSize,Mean,StandardDeviation,andStandardError
oftheMean. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
1.1 Mean. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
1.2 StandardDeviation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
1.3 StandardErroroftheMean. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
1.4 SampleSize,Mean,StandardDeviation,
andStandardErroroftheMean. . . . . . . . . . . . . . . . . . . . . . . . . . 4
1.4.1 UsingtheFill/Series/ColumnsCommands. . . . . . . . . . . . . 4
1.4.2 ChangingtheWidthofaColumn. . . . . . . . . . . . . . . . . . . 5
1.4.3 CenteringInformationinaRangeofCells. . . . . . . . . . . . . 6
1.4.4 NamingaRangeofCells. . . . . . . . . . . . . . . . . . . . . . . . . 8
1.4.5 FindingtheSampleSizeUsingthe¼COUNT
Function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
1.4.6 FindingtheMeanScoreUsingthe¼AVERAGE
Function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
1.4.7 FindingtheStandardDeviationUsing
the¼STDEVFunction. . . . . . . . . . .. . . . . . . . . . . . .. . . 10
1.4.8 FindingtheStandardErroroftheMean. . . . . . . . . . . . . . . 10
1.5 SavingaSpreadsheet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
1.6 PrintingaSpreadsheet. . . . .. . . . . . . . . . . . .. . . . . . . . . . . .. . . 13
1.7 FormattingNumbersinCurrencyFormat
(TwoDecimalPlaces). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
1.8 FormattingNumbersinNumberFormat(ThreeDecimalPlaces). . . 17
1.9 End-of-ChapterPracticeProblems. . . . . . . . . . . . . . . . . . . . . . . . 18
Reference. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
2 RandomNumberGenerator. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
2.1 CreatingFrameNumbersforGeneratingRandomNumbers. . . . . . 23
2.2 CreatingRandomNumbersinanExcelWorksheet. . . . . . . . . . . . 27
2.3 SortingFrameNumbersintoaRandomSequence. . . . . . . . . . . . . 28
xi