Opis: Statistical Analysis

Use Excel 2013's statistical tools to transform your data into knowledge Conrad 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 shortcutsIntroduction xi Using Excel for Statistical Analysis xi About You and About Excel xii Clearing Up the Terms xii Making Things Easier xiii The Wrong Box? xiv Wagging the Dog xvi What's in This Book xvi 1 About Variables and Values 1 Variables and Values 1 Recording Data in Lists 2 Scales of Measurement 4 Category Scales 5 Numeric Scales 7 Telling an Interval Value from a Text Value 8 Charting Numeric Variables in Excel 10 Charting Two Variables 10 Understanding Frequency Distributions 12 Using Frequency Distributions 15 Building a Frequency Distribution from a Sample 18 Building Simulated Frequency Distributions 26 2 How Values Cluster Together 29 Calculating the Mean 30 Understanding Functions, Arguments, and Results 31 Understanding Formulas, Results, and Formats 34 Minimizing the Spread 36 Calculating the Median 41 Choosing to Use the Median 41 Calculating the Mode 42 Getting the Mode of Categories with a Formula 47 From Central Tendency to Variability 54 3 Variability: How Values Disperse 55 Measuring Variability with the Range 56 The Concept of a Standard Deviation 58 Arranging for a Standard 59 Thinking in Terms of Standard Deviations 60 Calculating the Standard Deviation and Variance 62 Squaring the Deviations 65 Population Parameters and Sample Statistics 66 Dividing by N - 1 66 Bias in the Estimate 68 Degrees of Freedom 69 Excel's Variability Functions 70 Standard Deviation Functions 70 Variance Functions 71 4 How Variables Move Jointly: Correlation 73 Understanding Correlation 73 The Correlation, Calculated 75 Using the CORREL() Function 81 Using the Analysis Tools 84 Using the Correlation Tool 86 Correlation Isn't Causation 88 Using Correlation 90 Removing the Effects of the Scale 91 Using the Excel Function 93 Getting the Predicted Values 95 Getting the Regression Formula 96 Using TREND() for Multiple Regression 99 Combining the Predictors 99 Understanding "Best Combination" 100 Understanding Shared Variance 104 A Technical Note: Matrix Algebra and Multiple Regression in Excel 106 Moving on to Statistical Inference 107 5 How Variables Classify Jointly: Contingency Tables 109 Understanding One-Way Pivot Tables 109 Running the Statistical Test 112 Making Assumptions 117 Random Selection 118 Independent Selections 119 The Binomial Distribution Formula 120 Using the BINOM INV() Function 121 Understanding Two-Way Pivot Tables 127 Probabilities and Independent Events 130 Testing the Independence of Classifications 131 The Yule Simpson effect 137 Summarizing the Chi-Square Functions 140 Using CHISQ DIST() 140 Using CHISQ DIST RT() and CHIDIST() 141 Using CHISQ INV() 143 Using CHISQ INV RT() and CHIINV() 143 Using CHISQ TEST() and CHITEST() 144 Using Mixed and Absolute References to Calculate Expected Frequencies 145 Using the Pivot Table's Index Display 146 6 Telling the Truth with Statistics 149 A Context for Inferential Statistics 150 Establishing Internal Validity 151 Threats to Internal Validity 152 Problems with Excel's Documentation 156 The F-Test Two-Sample for Variances 157 Why Run the Test? 158 A Final Point 169 7 Using Excel with the Normal Distribution 171 About the Normal Distribution 171 Characteristics of the Normal Distribution 171 The Unit Normal Distribution 176 Excel Functions for the Normal Distribution 177 The NORM DIST() Function 177 The NORM INV() Function 180 Confidence Intervals and the Normal Distribution 182 The Meaning of a Confidence Interval 183 Constructing a Confidence Interval 184 Excel Worksheet Functions That Calculate Confidence Intervals 187 Using CONFIDENCE NORM() and CONFIDENCE() 188 Using CONFIDENCE T() 191 Using the Data Analysis Add-In for Confidence Intervals 192 Confidence Intervals and Hypothesis Testing 194 The Central Limit Theorem 194 Making Things Easier 196 Making Things Better 198 8 Testing Differences Between Means: The Basics 199 Testing Means: The Rationale 200 Using a z-Test 201 Using the Standard Error of the Mean 204 Creating the Charts 208 Using the t-Test Instead of the z-Test 216 Defining the Decision Rule 218 Understanding Statistical Power 222 9 Testing Differences Between Means: Further Issues 227 Using Excel's T DIST() and T INV() Functions to Test Hypotheses 227 Making Directional and Nondirectional Hypotheses 228 Using Hypotheses to Guide Excel's t-Distribution Functions 229 Completing the Picture with T DIST() 237 Using the T TEST() Function 238 Degrees of Freedom in Excel Functions 238 Equal and Unequal Group Sizes 239 The T TEST() Syntax 242 Using the Data Analysis Add-in t-Tests 255 Group Variances in t-Tests 255 Visualizing Statistical Power 260 When to Avoid t-Tests 261 10 Testing Differences Between Means: The Analysis of Variance 263 Why Not t-Tests? 263 The Logic of ANOVA 265 Partitioning the Scores 265 Comparing Variances 268 The F Test 273 Using Excel's Worksheet Functions for the F Distribution 277 Using F DIST() and F DIST RT() 277 Using F INV() and FINV() 278 The F Distribution 279 Unequal Group Sizes 280 Multiple Comparison Procedures 282 The Scheffe Procedure 284 Planned Orthogonal Contrasts 289 11 Analysis of Variance: Further Issues 293 Factorial ANOVA 293 Other Rationales for Multiple Factors 294 Using the Two-Factor ANOVA Tool 297 The Meaning of Interaction 299 The Statistical Significance of an Interaction 300 Calculating the Interaction Effect 302 The Problem of Unequal Group Sizes 307 Repeated Measures: The Two Factor Without Replication Tool 309 Excel's Functions and Tools: Limitations and Solutions 310 Mixed Models 312 Power of the F Test 312 12 Experimental Design and ANOVA 315 Crossed Factors and Nested Factors 315 Depicting the Design Accurately 317 Nuisance Factors 317 Fixed Factors and Random Factors 318 The Data Analysis Add-In's ANOVA Tools 319 Data Layout 320 Calculating the F Ratios 322 Adapting the Data Analysis Tool for a Random Factor 322 Designing the F Test 323 The Mixed Model: Choosing the Denominator 325 Adapting the Data Analysis Tool for a Nested Factor 326 Data Layout for a Nested Design 327 Getting the Sums of Squares 328 Calculating the F Ratio for the Nesting Factor 329 13 Statistical Power 331 Controlling the Risk 331 Directional and Nondirectional Hypotheses 332 Changing the Sample Size 332 Visualizing Statistical Power 333 Quantifying Power 335 The Statistical Power of t-Tests 337 Nondirectional Hypotheses 338 Making a Directional Hypothesis 340 Increasing the Size of the Samples 341 The Dependent Groups t-Test 342 The Noncentrality Parameter in the F Distribution 344 Variance Estimates 344 The Noncentrality Parameter and the Probability Density Function 348 Calculating the Power of the F Test 350 Calculating the Cumulative Density Function 350 Using Power to Determine Sample Size 352 14 Multiple Regression Analysis and Effect Coding: The Basics 355 Multiple Regression and ANOVA 356 Using Effect Coding 358 Effect Coding: General Principles 358 Other Types of Coding 359 Multiple Regression and Proportions of Variance 360 Understanding the Segue from ANOVA to Regression 363 The Meaning of Effect Coding 365 Assigning Effect Codes in Excel 368 Using Excel's Regression Tool with Unequal Group Sizes 370 Effect Coding, Regression, and Factorial Designs in Excel 372 Exerting Statistical Control with Semipartial Correlations 374 Using a Squared Semipartial to Get the Correct Sum of Squares 376 Using Trend() to Replace Squared Semipartial Correlations 377 Working With the Residuals 379 Using Excel's Absolute and Relative Addressing to Extend the Semipartials 381 15 Multiple Regression Analysis and Effect Coding: Further Issues 385 Solving Unbalanced Factorial Designs Using Multiple Regression 385 Variables Are Uncorrelated in a Balanced Design 386 Variables Are Correlated in an Unbalanced Design 388 Order of Entry Is Irrelevant in the Balanced Design 388 Order Entry Is Important in the Unbalanced Design 391 About Fluctuating Proportions of Variance 393 Experimental Designs, Observational Studies, and Correlation 394 Using All the LINEST() Statistics 397 Using the Regression Coefficients 398 Using the Standard Errors 398 Dealing with the Intercept 399 Understanding LINEST()'s Third, Fourth, and Fifth Rows 400 Getting the Regression Coefficients 406 Getting the Sum of Squares Regression and Residual 410 Calculating the Regression Diagnostics 412 How LINEST() Handles Multicollinearity 416 Forcing a Zero Constant 421 The Excel 2007 Version 422 A Negative R2? 425 Managing Unequal Group Sizes in a True Experiment 428 Managing Unequal Group Sizes in Observational Research 430 16 Analysis of Covariance: The Basics 433 The Purposes of ANCOVA 434 Greater Power 434 Bias Reduction 434 Using ANCOVA to Increase Statistical Power 435 ANOVA Finds No Significant Mean Difference 436 Adding a Covariate to the Analysis 437 Testing for a Common Regression Line 445 Removing Bias: A Different Outcome 447 17 Analysis of Covariance: Further Issues 453 Adjusting Means with LINEST() and Effect Coding 453 Effect Coding and Adjusted Group Means 458 Multiple Comparisons Following ANCOVA 461 Using the Scheffe Method 462 Using Planned Contrasts 466 The Analysis of Multiple Covariance 468 The Decision to Use Multiple Covariates 469 Two Covariates: An Example 470 Index 473

Szczegóły: Statistical Analysis

Tytuł: Statistical Analysis
Producent: Que Corporation
ISBN: 9780789753113
Rok produkcji: 2014
Ilość stron: 512
Oprawa: Miękka
Waga: 0.8 kg

Recenzje: Statistical Analysis