ch03ExcelManual.docx

ch03ExcelManual.docx

《ch03ExcelManual.docx》由会员分享，可在线阅读，更多相关《ch03ExcelManual.docx（16页珍藏版）》请在冰豆网上搜索。

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