Excel 2016 for Business Statistics: A Guide to Solving Practical Problems

257 Pages·2016·11.89 MB·English

Excel for Statistics
Thomas J. Quirk
Excel 2016 for
Business Statistics
A Guide to Solving Practical Problems
Excel for Statistics
ExcelforStatisticsisaseriesoftextbooksthatexplainhowtouseExceltosolve
statisticsproblemsinvariousfieldsofstudy.Professors,students,andpractitioners
will findthesebooksteachhow tomakeExcel work best intheir respective field.
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
critical statistics skills, making the books particularly applicable for statistics
coursestaughtoutsideofmathematicsorstatisticsdepartments.
Serieseditor:ThomasJ.Quirk
Thefollowingbooksareinthisseries:
T.J.Quirk,Excel2016forBusinessStatistics:AGuidetoSolvingPracticalProblems,Excelfor
Statistics.SpringerInternationalPublishingSwitzerland2016.
T.J. Quirk, Excel 2016 for Engineering Statistics: A Guide to Solving Practical Problems,
ExcelforStatistics.SpringerInternationalPublishingSwitzerland2016.
T.J.Quirk,M.Quirk,H.F.Horton,Excel2016forBiologicalandLifeSciencesStatistics:AGuide
toSolvingPracticalProblems,ExcelforStatistics.SpringerInternationalPublishingSwitzerland
2016.
T.J.Quirk,M.Quirk,H.F.Horton,Excel2016forPhysicalSciencesStatistics:AGuidetoSolving
PracticalProblems,ExcelforStatistics.SpringerInternationalPublishingSwitzerland2016.
T.J.Quirk,E.Rhiney,Excel2016forMarketingStatistics:AGuidetoSolvingPracticalProblems,
ExcelforStatistics.SpringerInternationalPublishingSwitzerland2016.
T.J.Quirk.Excel2016forEducationalandPsychologicalStatistics:AGuidetoSolvingPractical
Problems,ExcelforStatistics.SpringerInternationalPublishingSwitzerland2016.
T.J.Quirk,Excel2016forSocialScienceStatistics:AGuidetoSolvingPracticalProblems,Excelfor
Statistics.SpringerInternationalPublishingSwitzerland2016.
T.J.Quirk,S.Cummings,Excel2016forHealthServicesManagementStatistics:AGuidetoSolving
PracticalProblems.ExcelforStatistics.SpringerInternationalPublishingSwitzerland2016.
T.J.Quirk,J.Palmer-Schuyler,Excel2016forHumanResourceManagementStatistics:AGuideto
SolvingPracticalProblems,ExcelforStatistics.SpringerInternationalPublishingSwitzerland2016.
T.J.Quirk,M.Quirk,H.F.Horton.Excel2016forEnvironmentalSciencesStatistics:AGuideto
SolvingPracticalProblems,ExcelforStatistics.SpringerInternationalPublishingSwitzerland2016.
T.J.Quirk,J.Palmer-Schuyler,Excel2013forHumanResourceManagementStatistics:AGuideto
SolvingPracticalProblems,ExcelforStatistics.SpringerInternationalPublishingSwitzerland2016.
T.J.Quirk,S.Cummings,Excel2013forHealthServicesManagementStatistics:AGuidetoSolving
PracticalProblems,ExcelforStatistics.SpringerInternationalPublishingSwitzerland2016.
T.J.Quirk,M.Quirk,H.F.Horton,Excel2013forPhysicalSciencesStatistics:AGuidetoSolving
PracticalProblems,ExcelforStatistics.SpringerInternationalPublishingSwitzerland2016.
T.J.Quirk.Excel2013forEducationalandPsychologicalStatistics:AGuidetoSolvingPractical
Problems,ExcelforStatistics.SpringerInternationalPublishingSwitzerland2015.
T.J.Quirk,M.Quirk,H.F.Horton,Excel2013forBiologicalandLifeSciencesStatistics:AGuideto
SolvingPracticalProblems,ExcelforStatistics.SpringerInternationalPublishingSwitzerland2015.
T.J.Quirk,Excel2013forSocialScienceStatistics:AGuidetoSolvingPracticalProblems,Excel
forStatistics.SpringerInternationalPublishingSwitzerland2015.
T.J.Quirk,Excel2013forBusinessStatistics:AGuidetoSolvingPracticalProblems,Excelfor
Statistics.SpringerInternationalPublishingSwitzerland2015.
T.J.Quirk.Excel2013forEngineeringStatistics:AGuidetoSolvingPracticalProblems,Excel
forStatistics.SpringerInternationalPublishingSwitzerland2015.
AdditionalStatisticsbooksbyDr.TomQuirkthathavebeenpublishedbySpringer
T.J.Quirk,Excel2010forEngineeringStatistics:AGuidetoSolvingPracticalProblems,Springer
InternationalPublishingSwitzerland2014.
T.J. Quirk, S. Cummings, Excel 2010 for Health Services Management Statistics: A Guide to
SolvingPracticalProblems,SpringerInternationalPublishingSwitzerland2014.
T.J.Quirk,M.Quirk,H.Horton,Excel2010forPhysicalSciencesStatistics:AGuidetoSolving
PracticalProblems,SpringerInternationalPublishingSwitzerland2013.
T.J.Quirk,M.Quirk,H.F.Horton,Excel2010forBiologicalandLifeSciencesStatistics:AGuide
toSolvingPracticalProblems,SpringerScience+BusinessMediaNewYork2013.
T.J.Quirk,M.Quirk,H.F.Horton,Excel2007forBiologicalandLifeSciencesStatistics:AGuide
toSolvingPracticalProblems,SpringerScience+BusinessMediaNewYork2013.
T.J. Quirk, Excel 2010 for Social Science Statistics: A Guide to Solving Practical Problems,
SpringerScience+BusinessMediaNewYork2012.
T.J.Quirk,Excel2010forEducationalandPsychologicalStatistics:AGuidetoSolvingPractical
Problems,SpringerScience+BusinessMediaNewYork2012.
T.J.Quirk,Excel2007forBusinessStatistics:AGuidetoSolvingPracticalProblems,Springer
Science+BusinessMediaNewYork2012.
T.J. Quirk, Excel 2007 for Social Science Statistics: A Guide to Solving Practical Problems,
SpringerScience+BusinessMediaNewYork2012.
T.J.Quirk,Excel2007forEducationalandPsychologicalStatistics:AGuidetoSolvingPractical
Problems,SpringerScience+BusinessMediaNewYork2012.
T.J.Quirk,Excel2010forBusinessStatistics:AGuidetoSolvingPracticalProblems,Springer
Science+BusinessMedia2011.
Moreinformationaboutthisseriesathttp://www.springer.com/series/13491
Thomas J. Quirk
Excel 2016 for Business
Statistics
A Guide to Solving Practical Problems
ThomasJ.Quirk
WebsterUniversity
St.Louis,MO,USA
ExcelforStatistics
ISBN978-3-319-38958-5 ISBN978-3-319-38959-2 (eBook)
DOI10.1007/978-3-319-38959-2
LibraryofCongressControlNumber:2016941173
©SpringerInternationalPublishingSwitzerland2016
Thisworkissubjecttocopyright.AllrightsarereservedbythePublisher,whetherthewholeorpartof
the material is concerned, specifically the rights of translation, reprinting, reuse of illustrations,
recitation, broadcasting, reproduction on microfilms or in any other physical way, and transmission
or information storage and retrieval, electronic adaptation, computer software, or by similar or
dissimilarmethodologynowknownorhereafterdeveloped.
The use of general descriptive names, registered names, trademarks, service marks, etc. in this
publicationdoesnotimply,evenintheabsenceofaspecificstatement,thatsuchnamesareexempt
fromtherelevantprotectivelawsandregulationsandthereforefreeforgeneraluse.
Thepublisher,theauthorsandtheeditorsaresafetoassumethattheadviceandinformationinthis
book are believed to be true and accurate at the date of publication. Neither the publisher nor the
authors or the editors give a warranty, express or implied, with respect to the material contained
hereinorforanyerrorsoromissionsthatmayhavebeenmade.
Printedonacid-freepaper
ThisSpringerimprintispublishedbySpringerNature
TheregisteredcompanyisSpringerInternationalPublishingAGSwitzerland
Thisbookisdedicatedtothemorethan3,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.
Isalutethemall,andIthankthemforhelping
me to become a better teacher.
Thomas J. Quirk

Preface
Excel 2016 for Business Statistics: A Guide to Solving Practical Problems is
intended for anyone looking to learn the basics of applying Excel’s powerful
statisticaltoolstotheirbusinesscoursesorworkactivities.Ifunderstandingstatis-
ticsisn’tyourstrongestsuit,ifyouarenotespeciallymathematicallyinclined,orif
youarewaryofcomputers,thenthisistherightbookforyou.
Here you’ll learn how to use key statistical tests using Excel without being
overpowered by the underlying statistical theory. This book clearly and methodi-
cally shows and explains how to create and use these statistical tests to solve
practicalproblemsinbusiness.
Excel is an easily available computer program for students, instructors, and
managers.Itisalsoaneffectiveteachingandlearningtoolforquantitativeanalyses
inbusiness courses.The powerfulnumerical computational ability andthe graph-
icalfunctionsavailableinExcelmakelearningstatisticsmucheasierthaninyears
past.However,thisisthefirstbooktoshowExcel’scapabilitiestomoreeffectively
teach business statistics; it also focuses exclusively on this topic in an effort to
render the subject matter not only applicable and practical but also easy to com-
prehendandapply.
Uniquefeaturesofthisbook:
• Youwillbetoldeachstepoftheway,notonlyhowtouseExcelbutalsowhyyou
are doing each step so that you can understand what you are doing and not
merelylearnhowtousestatisticaltestsbyrote.
• Includes specific objectives embedded in the text for each concept, so you can
knowthepurposeoftheExcelsteps.
• Includes 167 color screenshots so that you can be sure you are performing the
Excelstepscorrectly
• This book is a tool that can be used either by itself or along with any good
statisticsbook.
• Practicalexamplesandproblemsaretakenfrombusiness.
vii
viii Preface
• Statisticaltheoryandformulasareexplainedinclearlanguagewithoutbogging
youdowninmathematicalfinepoints.
• YouwilllearnbothhowtowritestatisticalformulasusingExcelandhowtouse
Excel’sdrop-downmenusthatwillcreatetheformulasforyou.
• ThisbookdoesnotcomewithaCDofExcelfileswhichyoucanuploadtoyour
computer.Instead,you’llbeshownhowtocreateeachExcelfileyourself.Ina
work situation, your colleagues will not give you an Excel file; you will be
expected to create your own. This book will give you ample practice in devel-
opingthisimportantskill.
• Each chapter presents the steps needed to solve a practical business problem
using Excel. In addition, there are three practice problems at the end of each
chaptersoyoucan testyournewknowledgeofstatistics.Theanswerstothese
problemsappearinAppendixA.
• A“PracticeTest”isgiveninAppendixBtotestyourknowledgeattheendofthe
book.TheanswerstothesepracticalbusinessproblemsappearinAppendixC.
This book is appropriate for use in any course in business statistics (at both
undergraduate and graduate levels) as well as for managers who want to improve
theusefulnessoftheirExcelskills.
St.Louis,MO,USA ThomasJ.Quirk
Acknowledgments
Excel 2016 for Business Statistics: A Guide to Solving Practical Problems is the
result ofinspiration from three important people: my two daughters and mywife.
JenniferQuirkMcLaughlininvitedmetovisitherMBAclassesseveraltimesatthe
UniversityofWitwatersrandinJohannesburg,SouthAfrica.Thesevisitstoafirst-
rateMBAprogramconvincedmetherewasaneedforabooktoteachstudentshow
tosolvepracticalproblemsusingExcel.MeghanQuirk-Horton’sdoggeddedication
to learning the many statistical techniques needed to complete her Ph.D. disserta-
tion illustrated the need for a statistics book that would make this daunting task
moreuser-friendly.AndLynneBuckley-Quirkwasthenumber-onecheerleaderfor
this project from the beginning, always encouraging me and helping me remain
dedicatedtocompletingit.
Marc Strauss, our editor at Springer, caught the spirit of this idea in our first
phone conversation and shepherded this book through the idea stages until it
reached its final form. His encouragement and support were vital to this book
seeing the light of day. We thank him for being such an outstanding product
championthroughoutthisprocess.AndChristineCrigleratSpringerdidherusual
first-ratejobincoordinatingtheeditingandproductionofthisbook;sheisalwaysa
pleasuretoworkwith.
ThomasJ.Quirk
ix