ch03ExcelManual.docx
《ch03ExcelManual.docx》由会员分享,可在线阅读,更多相关《ch03ExcelManual.docx(16页珍藏版)》请在冰豆网上搜索。
![ch03ExcelManual.docx](https://file1.bdocx.com/fileroot1/2023-3/31/a21d93f9-5898-4e3b-8d5f-7cd6bbe0a27f/a21d93f9-5898-4e3b-8d5f-7cd6bbe0a27f1.gif)
ch03ExcelManual
Chapter3
DescriptiveStatistics
Chapter2presentedgraphicaltechniquesfororganizinganddisplayingdata.Eventhoughsuchgraphicaltechniquesallowtheresearchertomakesomegeneralobservationsabouttheshapeandspreadofthedata,amorecompleteunderstandingofthedatacanbeattainedbysummarizingthedatausingstatistics.Thischapterpresentssuchstatisticalmeasures,includingmeasuresofcentraltendency,measuresofvariability,andmeasuresofshape.Thecomputationofthesemeasuresisdifferentforungroupedandgroupeddata.
3.1MeasuresofCentralTendency:
UngroupedData
Onetypeofmeasurethatisusedtodescribeasetofdataisthemeasureofcentraltendency.Measuresofcentraltendencyyieldinformationaboutthecenter,ormiddlepart,ofagroupofnumbers.Measuresofcentraltendencydonotfocusonthespanofthedatasetorhowfarvaluesarefromthemiddlenumbers.Themeasuresofcentraltendencypresentedhereforungroupeddataarethemode,themedian,themean,percentiles,andquartiles.
Mode,Median,andMean
Themodeisthemostfrequentlyoccurringvalueinasetofdata.Themedianisthemiddlevalueinanorderedarrayofnumbers.Foranarraywithanoddnumberofterms,themedianisthemiddlenumber.Foranarraywithanevennumberofterms,themedianistheaverageofthetwomiddlenumbers.Themeanistheaverageofagroupofnumbersandiscomputedbysummingallnumbersanddividingbythenumberofnumbers.
DemonstrationProblem3.1
Shownbelowisalistofthe11largestmotorvehicleproducersintheworldandthenumberofvehiclesproducedbyeachin2009(citedintext).
AutoManufacturerProduction(millions)
ToyotaMotor7.2
GeneralMotors6.5
VolkswagenGroup6.1
FordMotor4.7
Hyundai4.6
PSAPeugeotCitroën3.0
Honda3.0
Nissan2.7
Fiat2.5
Suzuki2.4
Renault2.3
1.InputthedataintoExcel.SaveasDemo_3.1
2.ClickontheDatatabandDataAnalysis(ifyoudon'tseethisoption,seeChapter1toinstalltheAnalysisTookpak).
3.SelectDescriptiveStatisticsandthenselectintotheInputRangebox,selectacelltotherightofthedatafortheOutputRangeandselectSummarystatistics.Ifyouinputandselectedthelabel,selectLabelsinfirstrow(makesureitisinthecelldirectlyabovethedata).
4.Widenthecolumnwiththetexttoseeallofthetext(clickanddragordouble-clickthelinebetweenthecolumnletters).
5.Themeanusesallthedata,andeachdataiteminfluencesthemean.Itisalsoadisadvantagebecauseextremelylargeorsmallvaluescancausethemeantobepulledtowardtheextremevalue.
Remarks
Themeanusesallthedata,andeachdataiteminfluencesthemean.Itisalsoadisadvantagebecauseextremelylargeorsmallvaluescancausethemeantobepulledtowardtheextremevalue.Inthisdataset,themeanvalueis4.09andthemedianis3showingthatthelargevaluespullthemeantoahighervaluewhereasatypicalvaluewouldbemorelike3.Themodeormostcommonvalueisalso3.
Percentiles
Percentilesaremeasuresofcentraltendencythatdivideagroupofdatainto100parts.Thereare99percentilesbecauseittakes99dividerstoseparateagroupofdatainto100parts.Let'suseourdatasettofindspecificpercentilesusinganExcelfunction.
DemonstrationProblem3.2
1.InputthefollowingdataintoExcelinacolumn:
14,12,19,23,5,13,28,17.
2.Clickinacelltotherightofthedataandinputthefunction(=PERCENTILE(Selectrangeofdata,0.3).The30thpercentileisrepresentedby0.3.
3.Theansweris13.1andthewholenumberwouldbe13.Apercentilemayormaynotbeoneofthedatavalues.
Note:
TheRankandPercentilefeatureoftheDataAnalysistoolofExcelhasthecapabilityoforderingthedata,assigningrankstothedata,andyieldingthepercentilesofthedata.Toaccessthiscommand,clickonDataAnalysisandselectRankandPercentilefromthemenu.IntheRankandPercentiledialogbox,enterthelocationofthedatatobeanalyzedinInputRange.
Forthisdataset,theoutputlooksliketheoutputontheright:
Quartiles
Quartilesaremeasuresofcentraltendencythatdivideagroupofdataintofoursubgroupsorparts.Iftheobservationsareorderedfromsmallesttolargest,eachquartilerepresents25%oftheobservations.Thefirstquartile(Q1)representsthemedianoftheobservationsorderedfromtheminimumtotheoverallmedianM.ThesecondquartileistheoverallmedianMandrepresents50%ofallobservations.Thethirdquartilerepresentsthemedianoftheupper50%oftheobservations.Afive-numbersummarygivesacompletedescriptionofthedistribution,includingtheminimumnumber,Q1,M(median),Q3,andthemaximumnumber.Aboxplotisagraphofthefive-numbersummary.Side-by-sideboxplotsareusefultocompareseveraldistributions.
DemonstrationProblem3.3
1.OpentheDemo_3.3filefromthefoldertitled"DemonstrationProblemDataSets"onthestudentcompanionsitelocatedat
2.Belowthedata,inputthefollowinglabelsandformulasaccordingtotheinstructionsintheChapter1usingtheFunctionWizardorbyinputtingtheformulasmanually.YoucouldalsousetheQuartilefunctionforallofthevaluesbyinserting0,1,2,3,4forthesecondargument.Forexample,Maximumwouldbe=QUARTILE(B2:
B17,4).
3.
Toviewthefunctionsusedonaworksheet,thereisanoptioninExceltodisplayallequations.SelectFileOptionsAdvanced.ScrolldowntoDisplayoptionsforthisworksheetandselectShowformulasincellsinsteadoftheircalculatedresults.ClickOKandyouwillbeabletoviewalloftheformulasusedonthecurrentworksheet.Reversetheselectionwhenyouwanttoseeonlytheresults.YoucanalwaysclickonacellandseetheformulathatwasinputintheFormulaBar.
4.Theresultingvaluescalculatedareshownasfollows:
Note:
Thesevaluesarenotquitethesameasthevaluescalculatedinthetextbook.Thatisbecausethequartilesarecalculatedbyadifferentalgorithmindifferentsoftwareprograms.IfyouusetheMin,Max,andMedianfunctions,thosevalueswillbethesame.ThevaluesthatdifferareQ1andQ3.
3.1MeasuresofVariability:
UngroupedData
Businessresearcherscanuseanothergroupofanalytictools,measuresofvariability,todescribethespreadorthedispersionofasetofdata.Usingmeasuresofvariabilityinconjunctionwithmeasuresofcentraltendencymakespossibleamorecompletenumericaldescriptionofthedata.
Methodsofcomputingmeasuresofvariabilitydifferforungroupeddataandgroupeddata.Thissectionfocusesonsevenmeasuresofvariabilityforungroupeddata:
range,interquartilerange,meanabsolutedeviation,variance,standarddeviation,zscores,andcoefficientofvariation.
Range
Therangeisthedifferencebetweenthelargestvalueofadatasetandthesmallestvalueofaset.Althoughitisusuallyasinglenumericvalue,somebusinessresearchersdefinetherangeofdataastheorderedpairofsmallestandlargestnumbers(smallest,largest).Itisacrudemeasureofvariability,describingthedistancetotheouterboundsofthedataset.Anadvantageoftherangeisitseaseofcomputation.Adisadvantageoftherangeisthat,becauseitiscomputedwiththevaluesthatareontheextremesofthedata,itisaffectedbyextremevalues,anditsapplicationasameasureofvariabilityislimited.
InterquartileRange
Anothermeasureofvariabilityistheinterquartilerange.Theinterquartilerangeistherangeofvaluesbetweenthefirstandthirdquartile.Essentially,itistherangeofthemiddle50%ofthedataandisdeterminedbycomputingthevalueofQ3-Q1.Theinterquartilerangeisespeciallyusefulinsituationswheredatausersaremoreinterestedinvaluestowardthemiddleandlessinterestedinextremes.Inaddition,theinterquartilerangeisusedintheconstructionofbox-and-whiskerplots.
Theinterquartilerangevaluecandifferslightlywhenusingdifferentsoftwareprogramsduetotheunderlyingalgorithmsdefiningthequartiles.
DemonstrationProblem3.3cont.
1.OpentheDemo_3.3_Resultsfilefromthefoldertitled"DemonstrationProblemDataSets"onthestudentcompanionsitelocatedatorusetheresultscalculatedinthepreviousexerciseonquartiles.
2.TocalculatetherangeinExcel,useasimplesubtractionformula.Clickonacellbelowthequartilecalculationsandinput=andthenclickonthecomputedmaximumvalue,typea-andclickonthecomputedminimumvalue.Forourexample,itshouldlooklikethis:
Theresult:
3.Tocalculatetheinterquartilerange,useasimplesubtractionformulawithQ1andQ3.Clickonacellbelowtherangecalculationandinput=andthenclickonthecomputedQ3value,typea-andclickonthecomputedQ1value.Forourexample,itshouldlooklikethis:
Theresult:
MeanAbsoluteDeviation,Variance,andStandardDeviation
Threeothermeasuresofvariabilityarethevariance,thestandarddeviation,andthemeanabsolutedeviation.Thevarianceandstandarddeviationarewidelyusedinstatistics.Althoughthestandarddeviationhassomestand-alonepotential,theimportanceofvarianceandstandarddeviationliesmainlyintheirroleastoolsusedinconjunctionwithotherstatisticaldevices.
MeanAbsoluteDeviation
Themeanabsolutedeviation(MAD)istheaverageoftheabsolutevaluesofthedeviationsaroundthemeanforasetofnumbers.ThereisnofunctionsofthisinExcelbutyoucansetupatableanduseformulastocalculate.
MADProblem
1.Asmallcompanystartedaproductionlinetobuildcomputers.Durin