Excel 2016 for Social Work Statistics. A Guide to Solving Practical Problems

257 Pages·2017·11.09 MB·english

by  Thomas J. Quirk, Simone Cummings

Thomas J. Quirk • Simone Cummings
Excel 2016 for Social Work
Statistics
A Guide to Solving Practical Problems
ThomasJ.Quirk SimoneCummings
WebsterUniversity WebsterUniversity
St.Louis,MO,USA St.Louis,MO,USA
ExcelforStatistics
ISBN978-3-319-66220-6 ISBN978-3-319-66221-3 (eBook)
DOI10.1007/978-3-319-66221-3
LibraryofCongressControlNumber:2017951017
©SpringerInternationalPublishingAG2017
ThisSpringerimprintispublishedbySpringerNature
TheregisteredcompanyisSpringerInternationalPublishingAG
Theregisteredcompanyaddressis:Gewerbestrasse11,6330Cham,Switzerland
Preface
Excel 2016 for Social Work 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 social work 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
practicalproblemsinsocialwork.
Excel is an easily available computer program for students, instructors, and
managers.Itisalsoaneffectiveteachingandlearningtoolforquantitativeanalyses
in social work courses. The powerful numerical computational ability and the
graphical functions available in Excel make learning statistics much easier than
in years past. However, this is the first book to show Excel’s capabilities to more
effectivelyteachsocialworkstatistics;italsofocusesexclusivelyonthistopicinan
efforttorenderthesubjectmatternotonlyapplicableandpracticalbutalsoeasyto
comprehendandapply.
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 163 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.
• Practicalexamplesandproblemsaretakenfromsocialwork.
• 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.
• Eachchapterpresentsthestepsneededtosolveapracticalsocialworkproblem
using Excel. In addition, there are three practice problems at the end of each
chaptersoyoucan testyournewknowledgeofstatistics.Theanswerstothese
problemsappearinAppendixA.
• A“PracticeTest”isgiveninAppendixBtotestyourknowledgeattheendofthe
book. The answers to these practical social work problems appear in
AppendixC.
This book is appropriate for use inany course insocial work statistics (atboth
undergraduate and graduate levels) as well as for managers who want to improve
theusefulnessoftheirExcelskills.
Dr.TomQuirk,acurrentProfessorofMarketingattheGeorgeHerbertWalker
School of Business & Technology at Webster University in St. Louis, Missouri
(USA), teaches Marketing Statistics, Marketing Research, and Pricing Strategies.
Atthebeginningofhisacademiccareer,Prof.Quirkspentsixyearsineducational
researchattheAmericanInstitutesforResearchandEducationalTestingService.
HethentaughtSocialPsychology,EducationalPsychology,andGeneralPsychol-
ogy at Principia College inElsah, Illinois (USA). He has published articles in the
Journal of Educational Psychology, Journal of Educational Research, Review of
Educational Research, Journal of Educational Measurement, Educational Tech-
nology,TheElementary SchoolJournal,JournalofSecondaryEducation,Educa-
tional Horizons,and PhiDelta Kappan. Professor Quirk has published more than
20 articles in professional journals and presented more than 20 papers at profes-
sionalmeetings,includingannualmeetingsoftheAmericanEducationalResearch
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.
Prof.SimoneCummingsiscurrentlyDeanoftheWalkerSchoolofBusinessand
Technology at Webster University in St. Louis, Missouri, USA, where she also
teachesStatisticsforHealthAdministration,HealthcareFinance,andIntroduction
toHealthcareServices.SheholdsbothaB.S.B.A.andanM.H.AfromWashington
University in St. Louis and a Ph.D. in Health Management and Policy from the
University of North Carolina at Chapel Hill. Prof. Cummings has served on the
Board of the Association of University Programs in Health Administration and
currently serves as a Fellow for the Commission on Accreditation of Healthcare
ManagementEducation.Shehasexperienceconsultingandworkinginavarietyof
healthcare delivery organizations, including hospitals, clinics, and physician
groups. She has conducted clinical and health services research for more than ten
years and published articles in Health Services Research, Healthcare Executive,
Trustee,AcademicEmergencyMedicine,JournalofNationalMedicalAssociation,
andAcademicMedicine.
St.Louis,MO ThomasJ.Quirk
SimoneM.Cummings
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. . . . . . . . . . . . . . . . . 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 FindingtheStandardDeviationUsingthe¼STDEV
Function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
1.4.8 FindingtheStandardErroroftheMean. . . . . . . . . . . . 10
1.5 SavingaSpreadsheet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
1.6 PrintingaSpreadsheet. . . . .. . . . . .. . . . . .. . . . . .. . . . . . .. 13
1.7 FormattingNumbersinCurrencyFormat
(TwoDecimalPlaces). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
1.8 FormattingNumbersinNumberFormat
(ThreeDecimalPlaces). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
1.9 End-of-ChapterPracticeProblems. . . . . . . . . . . . . . . . . . . . . . 17
References. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
2 RandomNumberGenerator. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
2.1 CreatingFrameNumbersforGeneratingRandomNumbers. . . 21
2.2 CreatingRandomNumbersinanExcelWorksheet. . . . . . . . . . 25
2.3 SortingFrameNumbersintoaRandomSequence. . . . . . . . . . 26
2.4 PrintinganExcelFileSoThatAlloftheInformation
FitsontoOnePage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
2.5 End-of-ChapterPracticeProblems. . . . . . . . . . . . . . . . . . . . . . 33
3 ConfidenceIntervalAbouttheMeanUsingtheTINV
FunctionandHypothesisTesting. . . . . . . . . . . . . . . . . . . . . . . . . 35
3.1 ConfidenceIntervalAbouttheMean. . . . . . . . . . . . . . . . . . . . 35
3.1.1 HowtoEstimatethePopulationMean. . . . . . . . . . . . . 35
3.1.2 EstimatingtheLowerLimitandtheUpperLimit
ofthe95%ConfidenceIntervalAbouttheMean. . . . . . 36
3.1.3 EstimatingtheConfidenceIntervalfortheNumber
ofOutpatientVisitstoaClinic. . . . . . . . . . . . . . . . . . . 37
3.1.4 WhereDidtheNumber“1.96”ComeFrom?. . . . . . . . . 38
3.1.5 FindingtheValuefortintheConfidenceInterval
Formula. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
3.1.6 UsingExcel’sTINVFunctiontoFindtheConfidence
IntervalAbouttheMean. . . . . . . . . . . . . . . . . . . . . . . 40
3.1.7 UsingExceltoFindthe95%ConfidenceInterval
foraClinic’sOutpatientVisits. . . . . . . . . . . . . . . . . . . 40
3.2 HypothesisTesting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
3.2.1 HypothesesAlwaysRefertothePopulation
ThatYouAreStudying. . . . . . . . . . . . . . . . . . . . . . . . 46
3.2.2 TheNullHypothesisandtheResearch(Alternative)
Hypothesis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
3.2.3 The7StepsforHypothesis-TestingUsing
theConfidenceIntervalAbouttheMean. . . . . . . . . . . . 51
3.3 AlternativeWaystoSummarizetheResult
ofaHypothesisTest. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
3.3.1 DifferentWaystoAccepttheNullHypothesis. . . . . . . 58
3.3.2 DifferentWaystoRejecttheNullHypothesis. . . . . . . . 58
3.4 End-of-ChapterPracticeProblems. . . . . . . . . . . . . . . . . . . . . . 58
References. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
4 One-Groupt-TestfortheMean. . . . . . . . . . . . . . . . . . . . . . . . . . 65
4.1 The7STEPSforHypothesis-TestingUsingtheOne-Group
t-Test. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
4.1.1 STEP1:StatetheNullHypothesisandtheResearch
Hypothesis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
4.1.2 STEP2:SelecttheAppropriateStatisticalTest. . . . . . . 66
4.1.3 STEP3:DecideonaDecisionRulefortheOne-Group
t-Test. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
4.1.4 STEP4:CalculatetheFormulafortheOne-Group
t-Test. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
4.1.5 STEP5:FindtheCriticalValueoftinthet-Table
inAppendixE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68
4.1.6 STEP6:StatetheResultofYourStatisticalTest. . . . . . 69
4.1.7 STEP7:StatetheConclusionofYourStatistical
TestinPlainEnglish!. . . . . . . . . . . . . . . . . . . . . . . . . . 69
4.2 One-Groupt-TestfortheMean. . . . . . . . . . . . . . . . . . . . . . . . 70
4.3 CanYouUseEitherthe95%ConfidenceIntervalAbout
theMeanORtheOne-Groupt-TestWhen
TestingHypotheses?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
4.4 End-of-ChapterPracticeProblems. . . . . . . . . . . . . . . . . . . . . . 76
References. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
5 Two-Groupt-TestoftheDifferenceoftheMeansforIndependent
Groups. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
5.1 The9STEPSforHypothesis-TestingUsingtheTwo-Group
t-Test. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
5.1.1 STEP1:NameOneGroup,Group1,andtheOther
Group,Group2. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
5.1.2 STEP2:CreateaTableThatSummarizestheSample
Size,MeanScore,andStandardDeviationofEachGroup 82
5.1.3 STEP3:StatetheNullHypothesisandtheResearch
HypothesisfortheTwo-Groupt-Test. . . . . . . . . . . . . . 84
5.1.4 STEP4:SelecttheAppropriateStatisticalTest. . . . . . . 84
5.1.5 STEP5:DecideonaDecisionRulefortheTwo-Group
t-Test. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
5.1.6 STEP6:CalculatetheFormulafortheTwo-Group
t-Test. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
5.1.7 STEP7:FindtheCriticalValueoftinthet-Tablein
AppendixE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
5.1.8 STEP8:StatetheResultofYourStatisticalTest. . . . . . 86
5.1.9 STEP9:StatetheConclusionofYourStatisticalTest
inPlainEnglish!. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86
5.2 Formula#1:BothGroupsHaveaSampleSizeGreater
Than30. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
5.2.1 AnExampleofFormula#1fortheTwo-Group
t-Test. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
5.3 Formula#2:OneorBothGroupsHaveaSampleSizeLess
Than30. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
5.4 End-of-ChapterPracticeProblems. . . . . . . . . . . . . . . . . . . . . . 105
References. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
6 CorrelationandSimpleLinearRegression. . . . . . . . . . . . . . . . . . 109
6.1 WhatIsa“Correlation?”. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
6.1.1 UnderstandingtheFormulaforComputing
aCorrelation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
6.1.2 UnderstandingtheNineStepsforComputing
aCorrelation,r. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
6.2 UsingExceltoComputeaCorrelationBetween
TwoVariables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
6.3 CreatingaChartandDrawingtheRegressionLine
ontotheChart. . . .. . . .. . . .. . . .. . . .. . . .. . . .. . . .. . . .. 121
6.3.1 UsingExceltoCreateaChartandtheRegression
LineThroughtheDataPoints. . . . . . . . . . . . . . . . . . . . 123
6.4 PrintingaSpreadsheetSoThattheTableandChartFit
ontoOnePage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
6.5 FindingtheRegressionEquation. . . . . . . . . . . . . . . . . . . . . . . 132
6.5.1 InstallingtheDataAnalysisToolPakintoExcel. . . . . . 133
6.5.2 UsingExceltoFindtheSUMMARY
OUTPUTofRegression. . . . . . . . . . . . . . . . . . . . . . . . 136
6.5.3 FindingtheEquationfortheRegressionLine. . . . . . . . 139
6.5.4 UsingtheRegressionLinetoPredictthey-Value
foraGivenx-Value. . . . . . . . . . . . . . . . . . . . . . . . . . . 140
6.6 AddingtheRegressionEquationtotheChart. . . . . . . . . . . . . . 141
6.7 HowtoRecognizeNegativeCorrelationsintheSUMMARY
OUTPUTTable. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
6.8 PrintingOnlyPartofaSpreadsheetInsteadoftheEntire
Spreadsheet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
6.8.1 PrintingOnlytheTableandtheChartonaSeparate
Page. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
6.8.2 PrintingOnlytheChartonaSeparatePage. . . . . . . . . . 145
6.8.3 PrintingOnlytheSUMMARYOUTPUT
oftheRegressionAnalysisonaSeparatePage. . . . . . . 146
6.9 End-of-ChapterPracticeProblems. . . . . . . . . . . . . . . . . . . . . . 146
References. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152
7 MultipleCorrelationandMultipleRegression. . . . . . . . . . . . . . . 153
7.1 MultipleRegressionEquation. . . . . . . . . . . . . . . . . . . . . . . . . 153
7.2 FindingtheMultipleCorrelationandtheMultiple
RegressionEquation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156
7.3 UsingtheRegressionEquationtoPredict
FIRST-YEARGPA. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
7.4 UsingExceltoCreateaCorrelationMatrixinMultiple
Regression. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
7.5 End-of-ChapterPracticeProblems. . . . . . . . . . . . . . . . . . . . . . 165
References. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170
8 One-WayAnalysisofVariance(ANOVA). . . . . . . . . . . . . . . . . . . 171
8.1 UsingExceltoPerformaOne-WayAnalysisofVariance
(ANOVA). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
8.2 HowtoInterprettheANOVATableCorrectly. . . . . . . . . . . . . 175
8.3 UsingtheDecisionRulefortheANOVAF-Test. . . . . . . . . . . 176
8.4 TestingfortheDifferenceBetweenTwoGroupsUsingthe
ANOVAt-Test. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
8.4.1 ComparingClinicAvs.ClinicCinTimeRequiredto
ConductanInitialVisitUsingtheANOVAt-Test. . . . . 178
8.5 End-of-ChapterPracticeProblems. . . . . . . . . . . . . . . . . . . . . . 182
References. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187
Appendices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189
AppendixA:AnswerstoEnd-of-ChapterPracticeProblems. . . . . . . 189
AppendixB:PracticeTest. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223
AppendixC:AnswerstoPracticeTest. . . . . . . . . . . . . . . . . . . . . . . 235
AppendixD:StatisticalFormulas.. . . . .. . . . .. . . . .. . . . .. . . . .. 245
AppendixE:t-Table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247
Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249