One-way ANOVA & multiple comparisons in Excel tutorial

Setting up the one-way ANOVA · In the Options tab, leave the constraint option at a1=0. · In the Outputs tab (Means sub-tab), check a Tukey's test and a REGWQ ... Skiptomaincontent XLSTATisjoiningtheLumiverofamily.Learnmore. INSTALLATION&LICENSING Windowsinstallation Macinstallation Standardlicensing Networklicensing Cloudinstallation XLSTATtroubleshooting MyXLSTAT XLRISK GETTINGSTARTED OverviewofXLSTAT Datasetup Dataanalysis Outputscustomization TUTORIALS&GUIDES StatisticalGuides Datamanagement DescriptiveStatistics DataVisualization ExploratoryDataAnalysis Modelingdata Anova Regression Hypothesistesting Parametrictests Nonparametrictests Outlierstests Correlationtests MachineLearning Sensoryanalysis Marketinganalysis ConjointAnalysis TextMining DecisionAid TimeSeriesAnalysis MonteCarloSimulations PowerAnalysis StatisticalProcessControl DesignofExperiments Survivalanalysis Labdataanalysis Multiblockdataanalysis Pathmodeling XLSTATAI RinExcel Home TUTORIALS&GUIDES Modelingdata Anova One-wayANOVA&multiplecomparisonsinExceltutorial One-wayANOVA&multiplecomparisonsinExceltutorial Thistutorialshowshowtosetupandinterpretaone-wayAnalysisofVariance(ANOVA)followedbyTukey&DunnettmultiplecomparisonsinExcelusingtheXLSTATsoftware. Datasetforrunningaone-wayANOVA Thedatacorrespondtoanexperimentwhere4newtoothpasteformulaswereeachtestedon6differentpatientsinordertomeasuretheireffectonthewhitenessofteeth.Allpatientshadpreviouslyusedthesametoothpaste. UsingtheANOVAfunctionofXLSTATwewanttofindoutiftheresultsdifferaccordingtotheformulausedand,ifso,whichformulaisthemosteffective.Thecaseisaone-waybalancedANOVAbecausethereisonlyonefactor-theformula-andthenumberofrepetitionsisthesameforeachformula. Settinguptheone-wayANOVA OpenXLSTAT. SelecttheXLSTAT/Modelingdata/ANOVAcommand.Onceyouhaveclickedonthebutton,theANOVAdialogboxappears. SelectthedataontheExcelsheet.TotheDependentvariablecorrespondshere"Whiteness"whichvariabilitywewanttoexplainbytheeffectofthe"Toothpaste"formula,thelatterbeingtheQualitativeexplanatoryvariable. MakesuretochecktheoptionVariablelabels. Inthisexamplewewanttodisplaytheresultsonthesamesheetwherethedataarestored,sowechosetheRangeoptionandselectedthecellthatcorrespondstothetopleftcorneroftheresultsreporttobedisplayed. InXLSTAT,itispossibletoselectthedataintwodifferentwaysfortheANOVA.Thefirstisintheformofcolumns,onecolumnforthedependentvariable,anotherfortheexplanatoryvariable. Thesecondwaytoselectthedataisintabularform,witheachcolumnrepresentingamodalityoftheexplanatoryvariable. IntheOptionstab,leavetheconstraintoptionata1=0.ThismeansthatwewantthemodeltobebuiltusingtheassumptionthattheT1toothpastehasthebasiceffectonwhiteness:weknowtheaverageforT1isthelowestandthisguaranteesthattheothereffectswillbepositive. ApplyingaconstrainttotheANOVAmodelisnecessaryfortheoreticalreasons,butithasnoeffectontheresults(goodnessoffit,predictions).Theonlydifferenceitmakesisinthewaythemodelwillbewritten. IntheOutputstab(Meanssub-tab),checkaTukey'stestandaREGWQtestinthePariwisecomparisonsfield. ActivatetheComparisonswithacontroloptiontoruntwo-sidedDunnett'stest. ClickOKtolaunchthecomputations. Inthecontrolcategoryselectiondialogbox,choosetheT1controlgroupfortheDunnetttest. OncetheuserhasclickedontheOKbutton,thecomputationsresumeandtheresultsaredisplayed. Interpretingtheone-wayANOVAresults ThefirstresultsdisplayedbyXLSTATarethegoodnessoffitcoefficients,includingtheR²(coefficientofdetermination),theadjustedR²andseveralotherstatistics. Thecoefficientofdetermination(here0.56)givesafairideaofhowmuchofthevariabilityofthemodeledvariable(herethewhiteness)isbeingexplainedbytheexplanatoryvariables(herethetypeoftoothpaste);inourcase,wehave56%ofthevariabilityexplained.Theother44%arehiddeninothervariableswhicharenotavailable,andwhichthemodelhidesin"randomerrors". Theanalysisofvariancetableisaveryimportantresulttolookat(seebelow).Thisiswherewedeterminewhethertheexplanatoryvariable(thetoothpasteformula)bringssignificantinformation(nullhypothesisH0)tothemodelornot.Inotherwords,itisawayofaskingyourselfwhetheritisvalidtotakethemeantodescribethewholepopulation,oriftheinformationprovidedbythecategories(herethetoothpastetype)isofvalueornot. ThetestusedhereistheFisher'sFtest.GiventhattheprobabilitycorrespondingtotheFvalue,inthiscase,is0.001,itmeansthatwewouldtakea0.1%risktoconcludethatthenullhypothesis(noeffectofthetoothpasteformulas)iswrong. Sowecanconcludewithconfidencethatthereisaneffectofthetoothpasteformulasonthewhitenessofthepatients'teeth.NotethattheR²isnotverygood(0.56),meaningthatsomeoftheinformationofferingacomplementaryexplanationofthevariationsofthewhitenessismissing,whichisnorealsurprise. Thefollowingtablegivesdetailsonthemodel.Thistableishelpfulwhenpredictionsareneeded.Inthisparticularcase,itisnotveryuseful.WecanalreadynoticethatthetoothpasteT2hasaneffectwhich95%confidencerangeincludes0,indicatingthatthereisnoevidencethatT2isverydifferentfromT1. Thebarchartofthestandardizedcoefficientsallowtovisuallycomparetherelativeimpactofthecategoriesandtoseeiftheconfidenceintervalsinclude0ornot. Thenexttableshowstheresiduals.Wecanlookatthereducedresiduals(standardizedresiduals)morespecifically,residualswhich,giventheassumptionsoftheANOVAmodel,shouldbenormallydistributed.Thismeans,amongotherthings,that95%oftheresidualsshouldbeintheinterval[-1.96,1.96]. Allvaluesoutsidethisintervalarepotentialoutliersormightsuggestthatthenormalityassumptioniswrong.Itseemsherethatthereisonestrongoutlier(13thobservation)withastandardresidualequalto-2.64. Toexplainthedifference,oneshouldfirstverifythattherighttoothpastewasgiventothe13thpatient,andsecondly,oneshouldtrytounderstandwhytheresponsetotheformulawasn'tthesameasfortheotherpatients. Thehistogramofthestandardizedresidualsallowstoquicklyvisualizethestandardizedresidualsthatareoutoftheexpectedrange. Nowweobtaintheanswertoourinitialquestion:isthereasignificantdifferencebetweenthetreatments,andhowshouldthisdifferencebeclassified? Asshowninthenexttable,theTukey'sHSD(HonestlySignificantlyDifferent)testisappliedtoallpairwisedifferencesbetweenmeans.Theriskof5%wehavechosenisusedtodeterminethecriticalvalueq,whichiscomparedtothestandardizeddifferencebetweenthemeans. Basedonthep-valuesbelow(Pr>Diff),onlytwopairsappeartobesignificantlydifferent(T1,T3)and(T2,T3).Thiscanalsobeconfirmedbythe95%confidenceintervals(lastfourcolumns).Ifanintervaldoesnotcontainzero,thenwecanrejectthenullhypothesisthatthereisnosignificantdifferencebetweenthetwomeans. TheREQWQproceduregivesdifferentresults(seebelow),whichshowsthatoneneedstobeverycautiouswhenusingcomparisonmethods. Threepairsofcategoriesaredifferentinthiscase(T1andT4appeartobesignificantlydifferentwiththismethod).Thegroupingsgivenowthreesuperimposedgroupsofcategories. Next,weperformedaDunnett'stesttocompareeachcategorywiththecontrolcategoryT1.TheDunnett'stestagreeswiththeREQWQprocedurethattheT1andT4categoriesaresignificantlydifferent. Conclusionforthisone-wayANOVA Theconclusionisthatthe4toothpasteformulasshowsignificantlydifferenteffectsonwhiteness.AstheT1toothpasteisalreadyonthemarket,itisthetoothpasteT3orT4,whichshowasignificantincreaseinwhiteness,whichshouldbeselectedasnewcomerstothemarket. PleasenoticethatANOVAreliesonparametricassumptionsthatmustbeverifiedtoensurethereliabilityoftheoutput. Wasthisarticleuseful? Yes No Dataset demoANO.xlsm Includedin XLSTATBasic XLSTATBasic+ XLSTATPremium XLSTATSensory XLSTATLifeSciences XLSTATMarketing XLSTATForecasting XLSTATQuality Tableofcontents Datasetforrunningaone-wayANOVA Settinguptheone-wayANOVA Interpretingtheone-wayANOVAresults Conclusionforthisone-wayANOVA Similararticles MultivariateAnalysisofVariance(MANOVA)inExceltutorial RunrepeatedmeasuresANOVAusingmixedmodels ANOVA:AcomparisontestwithSPSSStatistics RepeatedmeasuresANOVAwithunbalanceddatausingmixedmodels ANCOVAanalysisinExceltutorial Contrastanalysisafteraone-wayANOVAinExcel Acompletestatisticaladd-inforMicrosoftExcel EnglishFrenchDeutschEspañolJapanese Products Trainings Trial Order Contactus Aboutus