book

Statistical Analysis: Microsoft Excel 2016

Name: Statistical Analysis: Microsoft Excel 2016
Author: Conrad Carlberg
ISBN: 9780134840437

by Conrad Carlberg

December 2017

Intermediate to advanced

576 pages

18h 2m

English

Addison-Wesley Professional

Read now

Unlock full access

Cover Page
Title Page
Copyright Page
Contents at a Glance
Contents
About This E-Book
About the author
Dedication
Acknowledgments
We Want to Hear from You!

Reader Services
Introduction
Using Excel for Statistical AnalysisAbout You and About ExcelClearing Up the TermsMaking Things EasierThe Wrong Box?Wagging the DogWhat’s in This Book
1 About Variables and Values
Variables and ValuesRecording Data in ListsMaking Use of ListsScales of MeasurementCategory ScalesNumeric ScalesTelling an Interval Value from a Text ValueCharting Numeric Variables in ExcelCharting Two VariablesUnderstanding Frequency DistributionsUsing Frequency DistributionsBuilding a Frequency Distribution from a SampleBuilding Simulated Frequency Distributions
2 How Values Cluster Together
Calculating the MeanUnderstanding Functions, Arguments, and ResultsUnderstanding Formulas, Results, and FormatsMinimizing the SpreadCalculating the MedianChoosing to Use the MedianStatic or Robust?Calculating the ModeGetting the Mode of Categories with a FormulaFrom Central Tendency to Variability
3 Variability: How Values Disperse
Measuring Variability with the RangeSample Size and the RangeVariations on the RangeThe Concept of a Standard DeviationArranging for a StandardThinking in Terms of Standard DeviationsCalculating the Standard Deviation and VarianceSquaring the DeviationsPopulation Parameters and Sample StatisticsDividing by N – 1Bias in the Estimate and Degrees of FreedomExcel’s Variability FunctionsStandard Deviation FunctionsVariance Functions
4 How Variables Move Jointly: Correlation
Understanding CorrelationThe Correlation, CalculatedUsing the CORREL() FunctionUsing the Analysis ToolsUsing the Correlation ToolCorrelation Isn’t CausationUsing CorrelationRemoving the Effects of the ScaleUsing the Excel FunctionGetting the Predicted ValuesGetting the Regression FormulaUsing TREND() for Multiple RegressionCombining the PredictorsUnderstanding “Best Combination”Understanding Shared VarianceA Technical Note: Matrix Algebra and Multiple Regression in Excel
5 Charting Statistics
Characteristics of Excel ChartsChart AxesDate Variables on Category AxesOther Numeric Variables on a Category AxisHistogram ChartsUsing a Pivot Table to Count the RecordsUsing Advanced Filter and FREQUENCY()The Data Analysis Add-in’s HistogramThe Built-in HistogramData Series AddressesBox-and-Whisker PlotsManaging OutliersDiagnosing AsymmetryComparing Distributions
6 How Variables Classify Jointly: Contingency Tables
Understanding One-Way Pivot TablesRunning the Statistical TestMaking AssumptionsRandom SelectionIndependent SelectionsThe Binomial Distribution FormulaUsing the BINOM.INV() FunctionUnderstanding Two-Way Pivot TablesProbabilities and Independent EventsTesting the Independence of ClassificationsAbout Logistic RegressionThe Yule Simpson EffectSummarizing the Chi-Square FunctionsUsing CHISQ.DIST()Using CHISQ.DIST.RT() and CHIDIST()Using CHISQ.INV()Using CHISQ.INV.RT() and CHIINV()Using CHISQ.TEST() and CHITEST()Using Mixed and Absolute References to Calculate Expected FrequenciesUsing the Pivot Table’s Index Display
7 Using Excel with the Normal Distribution
About the Normal DistributionCharacteristics of the Normal DistributionThe Unit Normal DistributionExcel Functions for the Normal DistributionThe NORM.DIST( ) FunctionThe NORM.INV( ) FunctionConfidence Intervals and the Normal DistributionThe Meaning of a Confidence IntervalConstructing a Confidence IntervalExcel Worksheet Functions That Calculate Confidence IntervalsUsing CONFIDENCE.NORM( ) and CONFIDENCE( )Using CONFIDENCE.T( )Using the Data Analysis Add-In for Confidence IntervalsConfidence Intervals and Hypothesis TestingThe Central Limit TheoremDealing with a Pivot Table IdiosyncrasyMaking Things EasierMaking Things Better
8 Telling the Truth with Statistics
A Context for Inferential StatisticsEstablishing Internal ValidityThreats to Internal ValidityProblems with Excel’s DocumentationThe F-Test Two-Sample for VariancesWhy Run the Test?ReproducibilityA Final Point
9 Testing Differences Between Means: The Basics
Testing Means: The RationaleUsing a z-TestUsing the Standard Error of the MeanCreating the ChartsUsing the t-Test Instead of the z-TestDefining the Decision RuleUnderstanding Statistical Power
10 Testing Differences Between Means: Further Issues
Using Excel’s T.DIST() and T.INV() Functions to Test HypothesesMaking Directional and Nondirectional HypothesesUsing Hypotheses to Guide Excel’s t-Distribution FunctionsCompleting the Picture with T.DIST()Using the T.TEST() FunctionDegrees of Freedom in Excel FunctionsEqual and Unequal Group SizesThe T.TEST() SyntaxUsing the Data Analysis Add-in t-TestsGroup Variances in t-TestsVisualizing Statistical PowerWhen to Avoid t-Tests
11 Testing Differences Between Means: The Analysis of Variance
Why Not t-Tests?The Logic of ANOVAPartitioning the ScoresComparing VariancesThe F-TestUsing Excel’s F Worksheet FunctionsUsing F.DIST() and F.DIST.RT()Using F.INV() and FINV()The F-DistributionUnequal Group SizesMultiple Comparison ProceduresThe Scheffé ProcedurePlanned Orthogonal Contrasts
12 Analysis of Variance: Further Issues
Factorial ANOVAOther Rationales for Multiple FactorsUsing the Two-Factor ANOVA ToolThe Meaning of InteractionThe Statistical Significance of an InteractionCalculating the Interaction EffectThe Problem of Unequal Group SizesRepeated Measures: The Two Factor Without Replication ToolExcel’s Functions and Tools: Limitations and SolutionsMixed ModelsPower of the F-Test
13 Experimental Design and ANOVA
Crossed Factors and Nested FactorsDepicting the Design AccuratelyNuisance FactorsFixed Factors and Random FactorsThe Data Analysis Add-In’s ANOVA ToolsData LayoutCalculating the F RatiosAdapting the Data Analysis Tool for a Random FactorDesigning the F-TestThe Mixed Model: Choosing the DenominatorAdapting the Data Analysis Tool for a Nested FactorData Layout for a Nested DesignGetting the Sums of SquaresCalculating the F Ratio for the Nesting FactorRandomized Block DesignsInteraction Between Factors and BlocksTukey’s Test for NonadditivityIncreasing Statistical PowerBlocks as Fixed or RandomSplit-Plot Factorial DesignsAssembling a Split-Plot Factorial DesignAnalysis of the Split-Plot Factorial Design
14 Statistical Power
Controlling the RiskDirectional and Nondirectional HypothesesChanging the Sample SizeVisualizing Statistical PowerThe Statistical Power of t-TestsNondirectional HypothesesMaking a Directional HypothesisIncreasing the Size of the SamplesThe Dependent Groups t-TestThe Noncentrality Parameter in the F-DistributionVariance EstimatesThe Noncentrality Parameter and the Probability Density FunctionCalculating the Power of the F-TestCalculating the Cumulative Density FunctionUsing Power to Determine Sample Size
15 Multiple Regression Analysis and Effect Coding: The Basics
Multiple Regression and ANOVAUsing Effect CodingEffect Coding: General PrinciplesOther Types of CodingMultiple Regression and Proportions of VarianceUnderstanding the Segue from ANOVA to RegressionThe Meaning of Effect CodingAssigning Effect Codes in ExcelUsing Excel’s Regression Tool with Unequal Group SizesEffect Coding, Regression, and Factorial Designs in ExcelExerting Statistical Control with Semipartial CorrelationsUsing a Squared Semipartial to Get the Correct Sum of SquaresUsing TREND() to Replace Squared Semipartial CorrelationsWorking with the ResidualsUsing Excel’s Absolute and Relative Addressing to Extend the Semipartials
16 Multiple Regression Analysis and Effect Coding: Further Issues
Solving Unbalanced Factorial Designs Using Multiple RegressionVariables Are Uncorrelated in a Balanced DesignVariables Are Correlated in an Unbalanced DesignOrder of Entry Is Irrelevant in the Balanced DesignOrder Entry Is Important in the Unbalanced DesignProportions of Variance Can FluctuateExperimental Designs, Observational Studies, and CorrelationUsing All the LINEST() StatisticsLooking Inside LINEST()Understanding How LINEST() Calculates Its ResultsGetting the Regression CoefficientsGetting the Sum of Squares Regression and ResidualCalculating the Regression DiagnosticsUnderstanding How LINEST() Handles MulticollinearityForcing a Zero ConstantThe Excel 2007 VersionA Negative R2?Managing Unequal Group Sizes in a True ExperimentManaging Unequal Group Sizes in Observational Research
17 Analysis of Covariance: The Basics
The Purposes of ANCOVAGreater PowerBias ReductionUsing ANCOVA to Increase Statistical PowerANOVA Finds No Significant Mean DifferenceAdding a Covariate to the AnalysisTesting for a Common Regression LineRemoving Bias: A Different Outcome
18 Analysis of Covariance: Further Issues
Adjusting Means with LINEST() and Effect CodingEffect Coding and Adjusted Group MeansMultiple Comparisons Following ANCOVAUsing the Scheffé MethodUsing Planned ContrastsThe Analysis of Multiple CovarianceThe Decision to Use Multiple CovariatesTwo Covariates: An ExampleWhen Not to Use ANCOVAIntact GroupsExtrapolation
Index

Content preview from Statistical Analysis: Microsoft Excel 2016

Index

A

a priori ordering approach, 443

absolute references

calculating expected frequencies with, 177

multiple regression, 429

accepting hypotheses, 156–157, 384

Add Chart Element button, 250

Add Records to Chart button, 35

Add-Ins command (Tools menu), 45, 98

Add-Ins dialog box, 45, 97

additivity, testing for, 355, 368–369

addresses, data series, 133–134

adjusted means, 486–488

adjusted group means, 499–504

calculating, 487–488

LINEST() and effect coding, 499–504

Advanced Data Filter, 129–131

alpha

alpha levels, 391

confidence intervals and, 198

definition of, 3, 157, 242–243, 377

establishing internal validity with, 213

F-tests, 223, 311–312

manipulating, 261–262

setting, 242–243

alternative hypothesis, 143, 155–156, 237

analysis of covariance. ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Statistical Analysis with Excel 2013 Advanced Skills

Publisher Resources

ISBN: 9780134840437

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Statistical Analysis: Microsoft Excel 2016

by Conrad Carlberg

Index

A

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.