Statistical Analysis with Excel® For Dummies®, 2nd Edition

Statistical Analysis with Excel® For Dummies®, 2nd Edition

by Joseph Schmuller
Released June 2009
Publisher(s): For Dummies
ISBN: 9780470454060

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

You too can understand the statistics of life, even if you're math-challenged!

What do you need to calculate? Manufacturing output? A curve for test scores? Sports stats? You and Excel can do it, and this non-intimidating guide shows you how. It demystifies the different types of statistics, how Excel functions and formulas work, the meaning of means and medians, how to interpret your figures, and more — in plain English.

  • Getting there — learn how variables, samples, and probability are used to get the information you want

  • Excel tricks — find out what's built into the program to help you work with Excel formulas

  • Playing with worksheets — get acquainted with the worksheet functions for each step

  • Graphic displays — present your data as pie graphs, bar graphs, line graphs, or scatter plots

  • What's normal? — understand normal distribution and probability

  • Hyping hypotheses — learn to use hypothesis testing with means and variables

  • When regression is progress — discover when and how to use regression for forecasting

  • What are the odds — work with probability, random variables, and binomial distribution

Open the book and find:

  • Ten statistical and graphical tips and traps

  • The difference between descriptive and inferential statistics

  • Why graphs are good

  • How to measure variations

  • What standard scores are and why they're used

  • When to use two-sample hypothesis testing

  • How to use correlations

  • Different ways of working with probability

Table of contents

  1. Copyright
  2. About the Author
  3. Author's Acknowledgments
  4. Publisher's Acknowledgments
  5. Introduction
    1. About This Book
    2. What You Can Safely Skip
    3. Foolish Assumptions
    4. How This Book Is Organized
      1. Part I: Statistics and Excel: A Marriage Made in Heaven
      2. Part II: Describing Data
      3. Part III: Drawing Conclusions from Data
      4. Part IV: Working with Probability
      5. Part V: The Part of Tens
      6. Appendix A: When Your Worksheet Is a Database
      7. Appendix B: The Analysis of Covariance
      8. Appendix C: Of Stems, Leaves, Boxes, Whiskers, and Smoothies
    5. Icons Used in This Book
    6. Where to Go from Here
  6. I. Statistics and Excel: A Marriage Made in Heaven
    1. 1. Evaluating Data in the Real World
      1. 1.1. The Statistical (And Related) Notions You Just Have to Know
        1. 1.1.1. Samples and populations
        2. 1.1.2. Variables: Dependent and independent
        3. 1.1.3. Types of data
        4. 1.1.4. A little probability
      2. 1.2. Inferential Statistics: Testing Hypotheses
        1. 1.2.1. Null and alternative hypotheses
        2. 1.2.2. Two types of error
      3. 1.3. What's New in Excel?
      4. 1.4. Some Things about Excel You Absolutely Have to Know
        1. 1.4.1. Autofilling cells
        2. 1.4.2. Referencing cells
      5. 1.5. What's New in This Edition?
    2. 2. Understanding Excel's Statistical Capabilities
      1. 2.1. Getting Started
      2. 2.2. Setting Up for Statistics
        1. 2.2.1. Worksheet functions in Excel 2007
        2. 2.2.2. Quickly accessing statistical functions
        3. 2.2.3. Array functions
        4. 2.2.4. What's in a name? An array of possibilities
        5. 2.2.5. Creating your own array formulas
        6. 2.2.6. Using data analysis tools
      3. 2.3. Accessing Commonly Used Functions
  7. II. Describing Data
    1. 3. Show and Tell: Graphing Data
      1. 3.1. Why Use Graphs?
      2. 3.2. Some Fundamentals
      3. 3.3. Excel's Graphics Capabilities
        1. 3.3.1. Inserting a chart
      4. 3.4. Becoming a Columnist
        1. 3.4.1. Stacking the columns
        2. 3.4.2. One more thing
      5. 3.5. Slicing the Pie
        1. 3.5.1. Pulling the slices apart
        2. 3.5.2. A word from the wise
      6. 3.6. Drawing the Line
      7. 3.7. Passing the Bar
      8. 3.8. The Plot Thickens
    2. 4. Finding Your Center
      1. 4.1. Means: The Lore of Averages
        1. 4.1.1. Calculating the mean
        2. 4.1.2. AVERAGE and AVERAGEA
        3. 4.1.3. AVERAGEIF and AVERAGEIFS
        4. 4.1.4. TRIMMEAN
        5. 4.1.5. Other means to an end
          1. 4.1.5.1. Geometric mean
          2. 4.1.5.2. Harmonic mean
      2. 4.2. Medians: Caught in the Middle
        1. 4.2.1. Finding the median
        2. 4.2.2. MEDIAN
      3. 4.3. Statistics À La Mode
        1. 4.3.1. Finding the mode
        2. 4.3.2. MODE
    3. 5. Deviating from the Average
      1. 5.1. Measuring Variation
        1. 5.1.1. Averaging squared deviations: Variance and how to calculate it
        2. 5.1.2. VARP and VARPA
        3. 5.1.3. Sample variance
        4. 5.1.4. VAR and VARA
      2. 5.2. Back to the Roots: Standard Deviation
        1. 5.2.1. Population standard deviation
        2. 5.2.2. STDEVP and STDEVPA
        3. 5.2.3. Sample standard deviation
        4. 5.2.4. STDEV and STDEVA
        5. 5.2.5. The missing functions: STDEVIF and STDEVIFS
          1. 5.2.5.1. A little logic
          2. 5.2.5.2. And now, back to the show
      3. 5.3. Related Functions
        1. 5.3.1. DEVSQ
        2. 5.3.2. Average deviation
        3. 5.3.3. AVEDEV
    4. 6. Meeting Standards and Standings
      1. 6.1. Catching Some Zs
        1. 6.1.1. Characteristics of z-scores
        2. 6.1.2. Bonds versus The Bambino
        3. 6.1.3. Exam scores
        4. 6.1.4. STANDARDIZE
      2. 6.2. Where Do You Stand?
        1. 6.2.1. RANK
        2. 6.2.2. LARGE and SMALL
        3. 6.2.3. PERCENTILE and PERCENTRANK
        4. 6.2.4. Data analysis tool: Rank and Percentile
    5. 7. Summarizing It All
      1. 7.1. Counting Out
        1. 7.1.1. COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS
      2. 7.2. The Long and Short of It
        1. 7.2.1. MAX, MAXA, MIN, and MINA
      3. 7.3. Getting Esoteric
        1. 7.3.1. SKEW
        2. 7.3.2. KURT
      4. 7.4. Tuning In the Frequency
        1. 7.4.1. FREQUENCY
        2. 7.4.2. Data analysis tool: Histogram
      5. 7.5. Can You Give Me a Description?
        1. 7.5.1. Data analysis tool: Descriptive Statistics
      6. 7.6. Instant Statistics
    6. 8. What's Normal?
      1. 8.1. Hitting the Curve
        1. 8.1.1. Digging deeper
        2. 8.1.2. Parameters of a normal distribution
        3. 8.1.3. NORMDIST
        4. 8.1.4. NORMINV
      2. 8.2. A Distinguished Member of the Family
        1. 8.2.1. NORMSDIST
        2. 8.2.2. NORMSINV
  8. III. Drawing Conclusions from Data
    1. 9. The Confidence Game: Estimation
      1. 9.1. What is a Sampling Distribution?
      2. 9.2. An EXTREMELY Important Idea: The Central Limit Theorem
        1. 9.2.1. Simulating the Central Limit Theorem
      3. 9.3. The Limits of Confidence
        1. 9.3.1. Finding confidence limits for a mean
        2. 9.3.2. CONFIDENCE
      4. 9.4. Fit to a t
        1. 9.4.1. TINV
    2. 10. One-Sample Hypothesis Testing
      1. 10.1. Hypotheses, Tests, and Errors
        1. 10.1.1. Hypothesis tests and sampling distributions
      2. 10.2. Catching Some Zs Again
        1. 10.2.1. ZTEST
      3. 10.3. t for One
        1. 10.3.1. TDIST
      4. 10.4. Testing a Variance
        1. 10.4.1. CHIDIST
        2. 10.4.2. CHIINV
    3. 11. Two-Sample Hypothesis Testing
      1. 11.1. Hypotheses Built for Two
      2. 11.2. Sampling Distributions Revisited
        1. 11.2.1. Applying the Central Limit Theorem
        2. 11.2.2. Zs once more
        3. 11.2.3. Data analysis tool: z-Test: Two Sample for Means
      3. 11.3. t for Two
        1. 11.3.1. Like peas in a pod: Equal variances
        2. 11.3.2. Like p's and q's: Unequal variances
        3. 11.3.3. TTEST
        4. 11.3.4. Data Analysis Tools: t-test: Two Sample
      4. 11.4. A Matched Set: Hypothesis Testing for Paired Samples
        1. 11.4.1. TTEST for matched samples
        2. 11.4.2. Data analysis tool: t-test: Paired Two Sample for Means
      5. 11.5. Testing Two Variances
        1. 11.5.1. Using F in conjunction with t
        2. 11.5.2. FTEST
        3. 11.5.3. FDIST
        4. 11.5.4. FINV
        5. 11.5.5. Data Analysis Tool: F-test Two Sample for Variances
    4. 12. Testing More Than Two Samples
      1. 12.1. Testing More Than Two
        1. 12.1.1. A thorny problem
        2. 12.1.2. A solution
        3. 12.1.3. Meaningful relationships
        4. 12.1.4. After the F-test
          1. 12.1.4.1. Planned comparisons
          2. 12.1.4.2. Unplanned comparisons
        5. 12.1.5. Data analysis tool: Anova: Single Factor
        6. 12.1.6. Comparing the means
      2. 12.2. Another Kind of Hypothesis, Another Kind of Test
        1. 12.2.1. Working with repeated measures ANOVA
        2. 12.2.2. Getting trendy
        3. 12.2.3. Data analysis tool: Anova: Two Factor Without Replication
        4. 12.2.4. Analyzing trend
    5. 13. Slightly More Complicated Testing
      1. 13.1. Cracking the Combinations
        1. 13.1.1. Breaking down the variances
        2. 13.1.2. Data analysis tool: Anova: Two-Factor Without Replication
      2. 13.2. Cracking the Combinations Again
        1. 13.2.1. Rows and columns
        2. 13.2.2. Interactions
        3. 13.2.3. The analysis
        4. 13.2.4. Data analysis tool: Anova: Two-Factor With Replication
    6. 14. Regression: Linear and Multiple
      1. 14.1. The Plot of Scatter
      2. 14.2. Graphing Lines
      3. 14.3. Regression: What a Line!
        1. 14.3.1. Using regression for forecasting
        2. 14.3.2. Variation around the regression line
        3. 14.3.3. Testing hypotheses about regression
          1. 14.3.3.1. Testing the fit
          2. 14.3.3.2. Testing the slope
          3. 14.3.3.3. Testing the intercept
      4. 14.4. Worksheet Functions for Regression
        1. 14.4.1. SLOPE, INTERCEPT, STEYX
        2. 14.4.2. FORECAST
        3. 14.4.3. Array function: TREND
          1. 14.4.3.1. Predicting y's for the x's in your sample
          2. 14.4.3.2. Predicting a new set of y's for a new set of x's
        4. 14.4.4. Array function: LINEST
      5. 14.5. Data Analysis Tool: Regression
        1. 14.5.1. Tabled output
        2. 14.5.2. Graphic output
      6. 14.6. Juggling Many Relationships at Once: Multiple Regression
      7. 14.7. Excel Tools for Multiple Regression
        1. 14.7.1. TREND revisited
        2. 14.7.2. LINEST revisited
        3. 14.7.3. Regression data analysis tool revisited
    7. 15. Correlation: The Rise and Fall of Relationships
      1. 15.1. Scatterplots Again
      2. 15.2. Understanding Correlation
      3. 15.3. Correlation and Regression
      4. 15.4. Testing Hypotheses About Correlation
        1. 15.4.1. Is a correlation coefficient greater than zero?
        2. 15.4.2. Do two correlation coefficients differ?
      5. 15.5. Worksheet Functions for Correlation
        1. 15.5.1. CORREL and PEARSON
        2. 15.5.2. RSQ
        3. 15.5.3. COVAR
      6. 15.6. Data Analysis Tool: Correlation
        1. 15.6.1. Tabled output
          1. 15.6.1.1. Multiple correlation
          2. 15.6.1.2. Partial correlation
          3. 15.6.1.3. Semipartial correlation
      7. 15.7. Data Analysis Tool: Covariance
      8. 15.8. Testing Hypotheses About Correlation
        1. 15.8.1. Worksheet Functions: FISHER, FISHERINV
  9. IV. Working with Probability
    1. 16. Introducing Probability
      1. 16.1. What is Probability?
        1. 16.1.1. Experiments, trials, events, and sample spaces
        2. 16.1.2. Sample spaces and probability
      2. 16.2. Compound Events
        1. 16.2.1. Union and intersection
        2. 16.2.2. Intersection again
      3. 16.3. Conditional Probability
        1. 16.3.1. Working with the probabilities
        2. 16.3.2. The foundation of hypothesis testing
      4. 16.4. Large Sample Spaces
        1. 16.4.1. Permutations
        2. 16.4.2. Combinations
      5. 16.5. Worksheet Functions
        1. 16.5.1. FACT
        2. 16.5.2. PERMUT
        3. 16.5.3. COMBIN
      6. 16.6. Random Variables: Discrete and Continuous
      7. 16.7. Probability Distributions and Density Functions
      8. 16.8. The Binomial Distribution
      9. 16.9. Worksheet Functions
        1. 16.9.1. BINOMDIST
        2. 16.9.2. NEGBINOMDIST
      10. 16.10. Hypothesis Testing with the Binomial Distribution
        1. 16.10.1. CRITBINOM
        2. 16.10.2. More on hypothesis testing
      11. 16.11. The Hypergeometric Distribution
        1. 16.11.1. HYPERGEOMDIST
    2. 17. More on Probability
      1. 17.1. Beta
        1. 17.1.1. BETADIST
        2. 17.1.2. BETAINV
      2. 17.2. Poisson
        1. 17.2.1. POISSON
      3. 17.3. Gamma
        1. 17.3.1. GAMMADIST
        2. 17.3.2. GAMMAINV
      4. 17.4. Exponential
        1. 17.4.1. EXPONDIST
    3. 18. A Career in Modeling
      1. 18.1. Modeling a Distribution
        1. 18.1.1. Plunging into the Poisson distribution
        2. 18.1.2. Using POISSON
        3. 18.1.3. Testing the model's fit
        4. 18.1.4. A word about CHITEST
        5. 18.1.5. Playing ball with a model
      2. 18.2. A Simulating Discussion
        1. 18.2.1. Taking a chance: The Monte Carlo method
        2. 18.2.2. Loading the dice
          1. 18.2.2.1. Data analysis tool: Random Number Generation
        3. 18.2.3. Simulating the Central Limit Theorem
  10. V. The Part of Tens
    1. 19. Ten Statistical and Graphical Tips and Traps
      1. 19.1. Significant Doesn't Always Mean Important
      2. 19.2. Trying to Not Reject a Null Hypothesis Has a Number of Implications
      3. 19.3. Regression Isn't Always linear
      4. 19.4. Extrapolating Beyond a Sample Scatterplot Is a Bad Idea
      5. 19.5. Examine the Variability Around a Regression Line
      6. 19.6. A Sample Can Be Too Large
      7. 19.7. Consumers: Know Your Axes
      8. 19.8. Graphing a Categorical Variable as Though It's a Quantitative Variable Is Just Wrong
      9. 19.9. Whenever Appropriate, Include Variability in Your Graph
      10. 19.10. Be Careful When Relating Statistics-Book Concepts to Excel
    2. 20. Ten Things (Twelve, Actually) That Didn't Fit in Any Other Chapter
      1. 20.1. Some Forecasting
        1. 20.1.1. A moving experience
        2. 20.1.2. How to be a smoothie, exponentially
      2. 20.2. Graphing the Standard Error of the Mean
      3. 20.3. Probabilities and Distributions
        1. 20.3.1. PROB
        2. 20.3.2. WEIBULL
      4. 20.4. Drawing Samples
      5. 20.5. Testing Independence: The True Use of CHITEST
      6. 20.6. Logarithmica Esoterica
        1. 20.6.1. What is a logarithm?
        2. 20.6.2. What is e?
        3. 20.6.3. LOGNORMDIST
        4. 20.6.4. LOGINV
        5. 20.6.5. Array Function: LOGEST
        6. 20.6.6. Array Function: GROWTH
          1. 20.6.6.1. Predicting y's for the x's in your sample
          2. 20.6.6.2. Predicting a new set of y's for a new set of x's
      7. 20.7. When Your Data Live Elsewhere
  11. A. When Your Worksheet Is a Database
    1. A.1. Introducing Excel Databases
      1. A.1.1. The Satellites database
      2. A.1.2. The criteria range
      3. A.1.3. The format of a database function
    2. A.2. Counting and Retrieving
      1. A.2.1. DCOUNT and DCOUNTA
        1. A.2.1.1. Getting to "or"
        2. A.2.1.2. Wildcards
      2. A.2.2. DGET
    3. A.3. Arithmetic
      1. A.3.1. DMAX and DMIN
      2. A.3.2. DSUM
      3. A.3.3. DPRODUCT
    4. A.4. Statistics
      1. A.4.1. DAVERAGE
      2. A.4.2. DVAR and DVARP
      3. A.4.3. DSTDEV and DSTDEVP
      4. A.4.4. According to Form
    5. A.5. Pivot Tables
  12. B. The Analysis of Covariance
    1. B.1. Covariance: A Closer Look
    2. B.2. Why You Analyze Covariance
    3. B.3. How You Analyze Covariance
    4. B.4. ANCOVA in Excel
      1. B.4.1. Method 1: ANOVA
      2. B.4.2. Method 2: Regression
      3. B.4.3. After the ANCOVA
    5. B.5. And One More Thing
  13. C. Of Stems, Leaves, Boxes, Whiskers, and Smoothies
    1. C.1. Stem-and-Leaf
    2. C.2. Boxes and Whiskers
    3. C.3. Data Smoothing

Product information

  • Title: Statistical Analysis with Excel® For Dummies®, 2nd Edition
  • Author(s): Joseph Schmuller
  • Release date: June 2009
  • Publisher(s): For Dummies
  • ISBN: 9780470454060