- ホーム
- > 洋書
- > 英文書
- > Business / Economics
Full Description
Use Excel 2013's statistical tools to transform your data into knowledgeConrad Carlberg shows how to use Excel 2013 to perform core statistical tasks every business professional, student, and researcher should master. Using real-world examples, Carlberg helps you choose the right technique for each problem and get the most out of Excel's statistical features, including recently introduced consistency functions. Along the way, he clarifies confusing statistical terminology and helps you avoid common mistakes.You'll learn how to use correlation and regression, analyze variance and covariance, and test statistical hypotheses using the normal, binomial, t, and F distributions. To help you make accurate inferences based on samples from a population, this edition adds two more chapters on inferential statistics, covering crucial topics ranging from experimental design to the statistical power of F tests.Becoming an expert with Excel statistics has never been easier! You'll find crystal-clear instructions, insider insights, and complete step-by-step projects-all complemented by extensive web-based resources.Master Excel's most useful descriptive and inferential statistical tools Tell the truth with statistics-and recognize when others don't Accurately summarize sets of values Infer a population's characteristics from a sample's frequency distribution Explore correlation and regression to learn how variables move in tandem Use Excel consistency functions such as STDEV.S() and STDEV.P() Test differences between two means using z tests, t tests, and Excel's Data Analysis Add-in Use ANOVA to test differences between more than two means Explore statistical power by manipulating mean differences, standard errors, directionality, and alpha Take advantage of Recommended PivotTables, Quick Analysis, and other Excel 2013 shortcuts
Contents
Introduction xiUsing Excel for Statistical Analysis xiAbout You and About Excel xiiClearing Up the Terms xiiMaking Things Easier xiiiThe Wrong Box? xivWagging the Dog xviWhat's in This Book xvi1 About Variables and Values 1Variables and Values 1Recording Data in Lists 2Scales of Measurement 4Category Scales 5Numeric Scales 7Telling an Interval Value from a Text Value 8Charting Numeric Variables in Excel 10Charting Two Variables 10Understanding Frequency Distributions 12Using Frequency Distributions 15Building a Frequency Distribution from a Sample 18Building Simulated Frequency Distributions 262 How Values Cluster Together 29Calculating the Mean 30Understanding Functions, Arguments, and Results 31Understanding Formulas, Results, and Formats 34Minimizing the Spread 36Calculating the Median 41Choosing to Use the Median 41Calculating the Mode 42Getting the Mode of Categories with a Formula 47From Central Tendency to Variability 543 Variability: How Values Disperse 55Measuring Variability with the Range 56The Concept of a Standard Deviation 58Arranging for a Standard 59Thinking in Terms of Standard Deviations 60Calculating the Standard Deviation and Variance 62Squaring the Deviations 65Population Parameters and Sample Statistics 66Dividing by N - 1 66Bias in the Estimate 68Degrees of Freedom 69Excel's Variability Functions 70Standard Deviation Functions 70Variance Functions 714 How Variables Move Jointly: Correlation 73Understanding Correlation 73The Correlation, Calculated 75Using the CORREL() Function 81Using the Analysis Tools 84Using the Correlation Tool 86Correlation Isn't Causation 88Using Correlation 90Removing the Effects of the Scale 91Using the Excel Function 93Getting the Predicted Values 95Getting the Regression Formula 96Using TREND() for Multiple Regression 99Combining the Predictors 99Understanding "Best Combination" 100Understanding Shared Variance 104A Technical Note: Matrix Algebra and Multiple Regression in Excel 106Moving on to Statistical Inference 1075 How Variables Classify Jointly: Contingency Tables 109Understanding One-Way Pivot Tables 109Running the Statistical Test 112Making Assumptions 117Random Selection 118Independent Selections 119The Binomial Distribution Formula 120Using the BINOM INV() Function 121Understanding Two-Way Pivot Tables 127Probabilities and Independent Events 130Testing the Independence of Classifications 131The Yule Simpson effect 137Summarizing the Chi-Square Functions 140Using CHISQ DIST() 140Using CHISQ DIST RT() and CHIDIST() 141Using CHISQ INV() 143Using CHISQ INV RT() and CHIINV() 143Using CHISQ TEST() and CHITEST() 144Using Mixed and Absolute References to Calculate Expected Frequencies 145Using the Pivot Table's Index Display 1466 Telling the Truth with Statistics 149A Context for Inferential Statistics 150Establishing Internal Validity 151Threats to Internal Validity 152Problems with Excel's Documentation 156The F-Test Two-Sample for Variances 157Why Run the Test? 158A Final Point 1697 Using Excel with the Normal Distribution 171About the Normal Distribution 171Characteristics of the Normal Distribution 171The Unit Normal Distribution 176Excel Functions for the Normal Distribution 177The NORM DIST() Function 177The NORM INV() Function 180Confidence Intervals and the Normal Distribution 182The Meaning of a Confidence Interval 183Constructing a Confidence Interval 184Excel Worksheet Functions That Calculate Confidence Intervals 187Using CONFIDENCE NORM() and CONFIDENCE() 188Using CONFIDENCE T() 191Using the Data Analysis Add-In for Confidence Intervals 192Confidence Intervals and Hypothesis Testing 194The Central Limit Theorem 194Making Things Easier 196Making Things Better 1988 Testing Differences Between Means: The Basics 199Testing Means: The Rationale 200Using a z-Test 201Using the Standard Error of the Mean 204Creating the Charts 208Using the t-Test Instead of the z-Test 216Defining the Decision Rule 218Understanding Statistical Power 2229 Testing Differences Between Means: Further Issues 227Using Excel's T DIST() and T INV() Functions to Test Hypotheses 227Making Directional and Nondirectional Hypotheses 228Using Hypotheses to Guide Excel's t-Distribution Functions 229Completing the Picture with T DIST() 237Using the T TEST() Function 238Degrees of Freedom in Excel Functions 238Equal and Unequal Group Sizes 239The T TEST() Syntax 242Using the Data Analysis Add-in t-Tests 255Group Variances in t-Tests 255Visualizing Statistical Power 260When to Avoid t-Tests 26110 Testing Differences Between Means: The Analysis of Variance 263Why Not t-Tests? 263The Logic of ANOVA 265Partitioning the Scores 265Comparing Variances 268The F Test 273Using Excel's Worksheet Functions for the F Distribution 277Using F DIST() and F DIST RT() 277Using F INV() and FINV() 278The F Distribution 279Unequal Group Sizes 280Multiple Comparison Procedures 282The Scheffe Procedure 284Planned Orthogonal Contrasts 28911 Analysis of Variance: Further Issues 293Factorial ANOVA 293Other Rationales for Multiple Factors 294Using the Two-Factor ANOVA Tool 297The Meaning of Interaction 299The Statistical Significance of an Interaction 300Calculating the Interaction Effect 302The Problem of Unequal Group Sizes 307Repeated Measures: The Two Factor Without Replication Tool 309Excel's Functions and Tools: Limitations and Solutions 310Mixed Models 312Power of the F Test 31212 Experimental Design and ANOVA 315Crossed Factors and Nested Factors 315Depicting the Design Accurately 317Nuisance Factors 317Fixed Factors and Random Factors 318The Data Analysis Add-In's ANOVA Tools 319Data Layout 320Calculating the F Ratios 322Adapting the Data Analysis Tool for a Random Factor 322Designing the F Test 323The Mixed Model: Choosing the Denominator 325Adapting the Data Analysis Tool for a Nested Factor 326Data Layout for a Nested Design 327Getting the Sums of Squares 328Calculating the F Ratio for the Nesting Factor 32913 Statistical Power 331Controlling the Risk 331Directional and Nondirectional Hypotheses 332Changing the Sample Size 332Visualizing Statistical Power 333Quantifying Power 335The Statistical Power of t-Tests 337Nondirectional Hypotheses 338Making a Directional Hypothesis 340Increasing the Size of the Samples 341The Dependent Groups t-Test 342The Noncentrality Parameter in the F Distribution 344Variance Estimates 344The Noncentrality Parameter and the Probability Density Function 348Calculating the Power of the F Test 350Calculating the Cumulative Density Function 350Using Power to Determine Sample Size 35214 Multiple Regression Analysis and Effect Coding: The Basics 355Multiple Regression and ANOVA 356Using Effect Coding 358Effect Coding: General Principles 358Other Types of Coding 359Multiple Regression and Proportions of Variance 360Understanding the Segue from ANOVA to Regression 363The Meaning of Effect Coding 365Assigning Effect Codes in Excel 368Using Excel's Regression Tool with Unequal Group Sizes 370Effect Coding, Regression, and Factorial Designs in Excel 372Exerting Statistical Control with Semipartial Correlations 374Using a Squared Semipartial to Get the Correct Sum of Squares 376Using Trend() to Replace Squared Semipartial Correlations 377Working With the Residuals 379Using Excel's Absolute and Relative Addressing to Extend the Semipartials 38115 Multiple Regression Analysis and Effect Coding: Further Issues 385Solving Unbalanced Factorial Designs Using Multiple Regression 385Variables Are Uncorrelated in a Balanced Design 386Variables Are Correlated in an Unbalanced Design 388Order of Entry Is Irrelevant in the Balanced Design 388Order Entry Is Important in the Unbalanced Design 391About Fluctuating Proportions of Variance 393Experimental Designs, Observational Studies, and Correlation 394Using All the LINEST() Statistics 397Using the Regression Coefficients 398Using the Standard Errors 398Dealing with the Intercept 399Understanding LINEST()'s Third, Fourth, and Fifth Rows 400Getting the Regression Coefficients 406Getting the Sum of Squares Regression and Residual 410Calculating the Regression Diagnostics 412How LINEST() Handles Multicollinearity 416Forcing a Zero Constant 421The Excel 2007 Version 422A Negative R2? 425Managing Unequal Group Sizes in a True Experiment 428Managing Unequal Group Sizes in Observational Research 43016 Analysis of Covariance: The Basics 433The Purposes of ANCOVA 434Greater Power 434Bias Reduction 434Using ANCOVA to Increase Statistical Power 435ANOVA Finds No Significant Mean Difference 436Adding a Covariate to the Analysis 437Testing for a Common Regression Line 445Removing Bias: A Different Outcome 44717 Analysis of Covariance: Further Issues 453Adjusting Means with LINEST() and Effect Coding 453Effect Coding and Adjusted Group Means 458Multiple Comparisons Following ANCOVA 461Using the Scheffe Method 462Using Planned Contrasts 466The Analysis of Multiple Covariance 468The Decision to Use Multiple Covariates 469Two Covariates: An Example 470Index 473
