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

265 Pages·2017·12.52 MB·English

by  Thomas J. Quirk

Excel for Statistics
Thomas J. Quirk
Eric Rhiney
Excel 2016 for
Advertising
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, E. Rhiney, Excel 2016 for Advertising Statistics: A Guide to Solving Practical
Problems,ExcelforStatistics.SpringerInternationalPublishingSwitzerland2017
T.J. Quirk, S. Cummings, Excel 2016 forSocial Work Statistics: A Guide to Solving Practical
Problems,ExcelforStatistics.SpringerInternationalPublishingAG2017.
T.J. Quirk, E. Rhiney, Excel 2016 for Marketing Statistics: A Guide to Solving Practical
Problems,ExcelforStatistics.SpringerInternationalPublishingSwitzerland2016.
T.J.Quirk,Excel2016forBusinessStatistics:AGuidetoSolvingPracticalProblems,Excelfor
Statistics.SpringerInternationalPublishingSwitzerland2016.
T.J.Quirk,Excel2016forEngineeringStatistics:AGuidetoSolvingPracticalProblems,Excel
forStatistics.SpringerInternationalPublishingSwitzerland2016.
T.J.Quirk,M.Quirk,H.F.Horton,Excel2016forBiologicalandLifeSciencesStatistics:AGuide
toSolvingPracticalProblems,ExcelforStatistics.SpringerInternationalPublishing2016.
T.J.Quirk.Excel2016forEducationalandPsychologicalStatistics:AGuidetoSolvingPractical
Problems,ExcelforStatistics.SpringerInternationalPublishingSwitzerland2016.
T.J.Quirk,Excel2016forSocialScienceStatistics:AGuidetoSolvingPracticalProblems,Excelfor
Statistics.SpringerInternationalPublishingSwitzerland2016.
T.J.Quirk,M.Quirk,H.F.Horton,Excel2016forPhysicalSciencesStatistics:AGuidetoSolving
PracticalProblems,ExcelforStatistics.SpringerInternationalPublishing2016.
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,M.Quirk,H.F.Horton,Excel2013forPhysicalSciencesStatistics:AGuidetoSolving
PracticalProblems.ExcelforStatistics.SpringerInternationalPublishingSwitzerland2016.
T.J.Quirk,S.Cummings,Excel2013forHealthServicesManagementStatistics:AGuidetoSolving
PracticalProblems.ExcelforStatistics.SpringerInternationalPublishingSwitzerland2016.
T.J.Quirk,J.Palmer-Schuyler,Excel2013forHumanResourceManagementStatistics:AGuideto
SolvingPracticalProblems,ExcelforStatistics.SpringerInternationalPublishingSwitzerland2016.
T.J.Quirk,Excel2013forBusinessStatistics:AGuidetoSolvingPracticalProblems,Excelfor
Statistics.SpringerInternationalPublishingSwitzerland2015.
T.J.Quirk,Excel2013forEngineeringStatistics:AGuidetoSolvingPracticalProblems.Excel
forStatistics.SpringerInternationalPublishingSwitzerland2015.
T.J.Quirk,M.Quirk,H.F.Horton,Excel2013forBiologicalandLifeSciencesStatistics:AGuideto
SolvingPracticalProblems,ExcelforStatistics.SpringerInternationalPublishing2015.
T.J.Quirk.Excel2013forEducationalandPsychologicalStatistics:AGuidetoSolvingPractical
Problems,ExcelforStatistics.SpringerInternationalPublishingSwitzerland2015.
T.J.Quirk,Excel2013forSocialScienceStatistics:AGuidetoSolvingPracticalProblems,Excel
forStatistics.SpringerInternationalPublishingSwitzerland2015.
T.J.Quirk,M.Quirk,H.F.Horton,Excel2013forEnvironmentalSciencesStatistics:AGuideto
SolvingPracticalProblems.ExcelforStatistics.SpringerInternationalPublishingSwitzerland2015.
AdditionalStatisticsbooksbyDr.TomQuirkthathavebeenpublishedbySpringer
T.J.Quirk,Excel2010forBusinessStatistics:AGuidetoSolvingPracticalProblems.Springer
Science+BusinessMedia2011.
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.
Moreinformationaboutthisseriesathttp://www.springer.com/series/13491
Thomas J. Quirk • Eric Rhiney
Excel 2016 for Advertising
Statistics
A Guide to Solving Practical Problems
ThomasJ.Quirk EricRhiney
ProfessorofMarketing AssociateProfessorofMarketing
WebsterUniversity WebsterUniversity
St.Louis,MO,USA St.Louis,MO,USA
ExcelforStatistics
ISBN978-3-319-72103-3 ISBN978-3-319-72104-0 (eBook)
https://doi.org/10.1007/978-3-319-72104-0
LibraryofCongressControlNumber:2017959762
©SpringerInternationalPublishingAG2017
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
hereinor for anyerrors oromissionsthat may havebeenmade. Thepublisher remainsneutralwith
regardtojurisdictionalclaimsinpublishedmapsandinstitutionalaffiliations.
Printedonacid-freepaper
ThisSpringerimprintispublishedbySpringerNature
TheregisteredcompanyisSpringerInternationalPublishingAG
Theregisteredcompanyaddressis:Gewerbestrasse11,6330Cham,Switzerland
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. I
salute them all, and I thank them for helping
me to become a better teacher.
Thomas J. Quirk
I would like to dedicate this work to my
beautiful and patient wife, Tachelle, and my
kids, Anaya, Haley, and Joshua. They all
managed to make do when I have to work
from home. I would also like to recognize my
mother, Vera Rhiney, and my mother-in-law,
AngelicaCleveland,whoconstantlystepinto
provide support to me and my family.
Eric Rhiney
Preface
Excel 2016 for Advertising Statistics: A Guide to Solving Practical Problems is
intended for anyone looking to learn the basics of applying Excel’s powerful
statistical tools to their advertising courses or work activities. If understanding
statisticsisn’tyourstrongestsuit,youarenotespeciallymathematicallyinclined,or
ifyouarewaryofcomputers,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
practicalproblemsinadvertising.
Excelisaneasilyavailablecomputerprogramforstudents,instructors,andman-
agers. It is also an effective teaching and learning tool for quantitative analyses in
advertisingcourses.Thepowerfulnumericalcomputationalabilityandthegraphical
functionsavailable in Excelmakelearning statisticsmuch easier than in years past.
However,thisisthefirstbooktoshowExcel’scapabilitiestomoreeffectivelyteach
advertisingstatistics;italsofocusesexclusivelyonthistopicinanefforttorenderthe
subjectmatternotonlyapplicableandpracticalbutalsoeasytocomprehendandapply.
Uniquefeaturesofthisbook:
• ThisbookisappropriateforuseinanycourseinAdvertisingStatistics(atboth
undergraduateandgraduatelevels)aswellasformanagerswhowanttoimprove
theusefulnessoftheirExcelskills.
• Includes 167 color screenshots so that you can be sure you are performing the
Excelstepscorrectly.
• 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.
• This book is a tool that can be used either by itself or along with any good
statisticsbook.
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.
• Eachchapterpresentsthestepsneededtosolveapracticaladvertisingproblem
using Excel. In addition, there are three practice problems at the end of each
chaptersoyoucan testyournewknowledgeofstatistics.Theanswerstothese
problemsappearinAppendixA.
• A“Practice Test”is given inAppendix Bto test yourknowledge at the end of
the book. The answers to these practical advertising problems appear in
AppendixC.
Thomas J. Quirk is currently a Professor of Marketing in the Walker School of
Business&TechnologyatWebsterUniversityinSt.Louis,Missouri(USA),where
he teaches marketing statistics, marketing research, and pricing strategies. He has
published articles in The Journal of Educational Psychology, Journal of Educa-
tional Research, Review of Educational Research, Journal of Educational Mea-
surement, Educational Technology, The Elementary School Journal, Journal of
Secondary Education, Educational Horizons, and Phi Delta Kappan. Professor
Quirk has published more than 20 articles in professional journals and presented
morethan20papersatprofessionalmeetings.HeholdsaB.S.inmathematicsfrom
John Carroll University, both an M.A. in education and a Ph.D. in educational
psychology from Stanford University, and an M.B.A. from the University of
Missouri-St.Louis.
Prof.EricRhineyiscurrentlyanAssociateProfessorofMarketingintheWalker
School of Business & Technology at Webster University in St. Louis, Missouri
(USA)whereheteachesresearchdesign,marketingresearch,andmarketingstrat-
egies. He holds a B.S.B.A. with an emphasis in marketing from the University of
CentralMissouri,anM.B.A.withanemphasisinmarketingfromWebsterUniver-
sity,andaPh.D.inmarketingandinternationalbusinessfromSt.LouisUniversity.
Hedidmarketingresearchprofessionallyforover10yearsengaginginresearchfor
companiessuchasPizzaHut,Monsanto,Chrysler,andHardee’s.Heisinvolvedin
anumberofquantitativeresearchstudiesfocusedonin-groupout-grouporientation
onconsumerattitudes,digitalmarketingbehavior,andcross-culturalmarketingand
has presented his work at a number of conferences including the American Mar-
keting Association, the International Business Association, and the Marketing
ManagementAssociationandtheUniversityofMissouri-St.LouisDigitalMarket-
ingConference.
St.Louis,MO ThomasJ.Quirk
EricRhiney
Acknowledgments
Excel2016forAdvertisingStatistics:AGuidetoSolvingPracticalProblemsisthe
result ofinspiration from three important people: my two daughters and mywife.
JenniferQuirkMcLaughlininvitedmetovisitherM.B.A.classesseveraltimesat
the University of Witwatersrand in Johannesburg, South Africa. These visits to a
first-rate M.B.A. 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 techniques needed to complete
herPh.D.dissertationillustratedtheneedforastatisticsbookthatwouldmakethis
daunting task more user-friendly. And LynneBuckley-Quirk was the number-one
cheerleaderforthisprojectfromthebeginning,alwaysencouragingmeandhelping
meremaindedicatedtocompletingit.
ThomasJ.Quirk
I would like to acknowledge Tom Quirk, who not only as a former professor of
mine but also as a wonderful colleague has always been a fantastic mentor con-
stantly encouraging me. Furthermore, I would like to acknowledge the St. Louis
University(SLU)Ph.D.ProgramaswellasSLUandWebsterUniversityadminis-
trators,facultycolleagues,andstaff.YouallarealwaysthereformeandIcouldnot
askforabetterworkfamily.
EricRhiney
Mike Penn, our Editor at Springer, caught the spirit of this idea in our first
communication 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 champion
throughoutthisprocess.
ix
Contents
1 SampleSize,Mean,StandardDeviation,andStandard
ErroroftheMean. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
1.1 Mean. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
1.2 StandardDeviation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
1.3 StandardErroroftheMean. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
1.4 SampleSize,Mean,StandardDeviation,andStandard
ErroroftheMean. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
1.4.1 UsingtheFill/Series/ColumnsCommands. . . . . . . . . . . . 4
1.4.2 ChangingtheWidthofaColumn. . . . . . . . . . . . . . . . . . . 6
1.4.3 CenteringInformationinaRangeofCells. . . . . . . . . . . . 7
1.4.4 NamingaRangeofCells. . . . . . . . . . . . . . . . . . . . . . . . . 8
1.4.5 FindingtheSampleSizeUsingthe¼COUNT
Function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
1.4.6 FindingtheMeanScoreUsingthe¼AVERAGE
Function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
1.4.7 FindingtheStandardDeviationUsingthe¼STDEV
Function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
1.4.8 FindingtheStandardErroroftheMean. . . . . . . . . . . . . . 11
1.5 SavingaSpreadsheet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
1.6 PrintingaSpreadsheet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
1.7 FormattingNumbersinCurrencyFormat
(TwoDecimalPlaces). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
1.8 FormattingNumbersinNumberFormat
(ThreeDecimalPlaces). .. . . . .. . . . .. . . .. . . . .. . . . .. . . . .. 18
1.9 End-of-ChapterPracticeProblems. . . . . . . . . . . . . . . . . . . . . . . . 18
References. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
2 RandomNumberGenerator. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
2.1 CreatingFrameNumbersforGeneratingRandomNumbers. . . . . 23
2.2 CreatingRandomNumbersinanExcelWorksheet. . . . . . . . . . . 27
xi