|
Published:
January 19, 2022

Statistical Analysis with Excel For Dummies

Overview

Become a stats superstar by using Excel to reveal the powerful secrets of statistics

Microsoft Excel offers numerous possibilities for statistical analysis—and you don’t have to be a math wizard to unlock them. In Statistical Analysis with Excel For Dummies, fully updated for the 2021 version of Excel, you’ll hit the ground running with straightforward techniques and practical guidance to unlock the power of statistics in Excel.

Bypass unnecessary jargon and skip right to mastering formulas, functions, charts, probabilities, distributions, and correlations. Written for professionals and students without a background in statistics or math, you’ll learn to create, interpret, and translate statistics—and have fun doing it!

In this book you’ll find out

how to:

  • Understand, describe, and summarize any kind of data, from sports stats to sales figures
  • Confidently draw conclusions from your analyses, make accurate predictions, and calculate correlations
  • Model the probabilities of future outcomes based on past data
  • Perform statistical analysis on any platform: Windows, Mac, or iPad
  • Access additional resources and practice templates through Dummies.com

For anyone who’s ever wanted to unleash the full potential of statistical analysis in Excel—and impress your colleagues or classmates along the way—Statistical Analysis with Excel For Dummies walks you through the foundational concepts of analyzing statistics and the step-by-step methods you use to apply them.

Read More

About The Author

Joseph Schmuller works on the Digital & Enterprise Architecture Team at Availity. He has taught statistics at the undergraduate and graduate levels. He has created and delivered courses for LinkedIn Learning, and he is the author of all previous editions of Statistical Analysis with Excel For Dummies.

Sample Chapters

statistical analysis with excel for dummies

CHEAT SHEET

Excel offers a wide range of statistical functions you can use to calculate a single value or an array of values in your Excel worksheets. The Excel Analysis Toolpak is an add-in that provides even more statistical analysis tools.Some Excel worksheet functions for statistical analysisExcel can help you make all sorts of calculations.

HAVE THIS BOOK?

Articles from
the book

To help you understand statistical analysis with Excel, it helps to simulate the Central Limit Theorem. It almost doesn’t sound right. How can a population that’s not normally distributed result in a normally distributed sampling distribution?To give you an idea of how the Central Limit Theorem works, there is a simulation.
The world of statistics is full of pitfalls, but it’s also full of opportunities. Whether you’re a user of statistics or someone who has to interpret them, it’s possible to fall into the pitfalls. It’s also possible to walk around them. Here are ten tips and traps from the areas of hypothesis testing, regression, correlation, and graph.
In addition to Excel’s built-in array formulas, you can create your own. To help things along, you can incorporate named arrays. The image below shows two named arrays, X and Y, in columns C and D, respectively. X refers to C2 through C5 (not C1 through C5), and Y refers to D2 through D5 (not D1 through D5). XY is the column header for column F.
GROWTH is Excel’s curvilinear regression's answer to TREND. You can use this function in two ways: to predict a set of y-values for the x-values in your sample or to predict a set of y-values for a new set of x-values. Predicting y's for the x's in your sample This image shows GROWTH set up to calculate y's for the x's that are already there.
An array formula calculates a set of values rather than just one. Here are Excel's statistical array functions. Each one returns an array of values into a selected array of cells. Function Calculates An Array Of … FREQUENCY Frequencies of values in a set of values MODE.MULT Modes of a set of numbers LINEST Re
Excel's Analysis ToolPak is a helpful add-in that provides an extensive set of statistical analysis tools. Here are some of the tools in the ToolPak. Tool What it Does Anova: Single Factor Analysis of variance for two or more samples Anova: Two Factor with Replication Analysis of variance with two independent variables, and multiple observations in each combination of the levels of the variables.
Excel offers you functions for working with binomial distribution and negative binomial distribution. These distributions are computation intensive, so let’s get to the worksheet functions right away. BINOM.DIST and BINOM.DIST.RANGE These are Excel's worksheet functions for the binomial distribution. Use BINOM.
Excel provides two worksheet functions for calculating correlation — and, they do exactly the same thing in exactly the same way! Why Excel offers both CORREL and PEARSON is unclear, but there you have it. Those are the two main correlation functions.The others are RSQ, COVARIANCE.P, and COVARIANCE.S. RSQ calculates the coefficient of determination (the square of the correlation coefficient).
Excel provides functions that help you with factorials, permutations, and combinations. In the world of statistical analysis, these can be very useful. Keep reading to find out how to use these functions. FACT FACT, which computes factorials, is surprisingly not categorized as Statistical. Instead, you'll find it on the Math & Trig Functions menu.
Here are some probability-related worksheet functions that you can use in Excel 2016 to help with statistical analysis. Although they’re a little on the esoteric side, you might find some use for them. PROB If you have a probability distribution of a discrete random variable and you want to find the probability that the variable takes on a particular value, PROB is for you.
When you create a graph in Excel and your data are means, it's a good idea to include the standard error of each mean in your graph. This gives the viewer an idea of the spread of scores around each mean.Here’s an example of a situation where this arises. The data are (fictional) test scores for four groups of people.
Spearman’s correlation coefficient, rS, was the earliest non-parametric test based on ranks. For a sample of individuals each measured on two variables in Excel , the idea is to rank each score within its own variable. Then, for each individual subtract one rank from the other. If correlation is perfect (in the positive direction), all the differences are zero.
Added as Power View to Excel 2013, 3D Maps is the current incarnation in Excel 2016. As its name implies, 3D Maps adds three-dimensional visualization. Here, you learn how to use 3D Maps to create a globe with data on numerous countries.The image below shows part of a spreadsheet with data on life expectancy at birth, expected years of schooling, and mean years of schooling for 188 countries.
Someday, Excel’s Analysis ToolPak might have a choice labeled ANOVA: Mixed Design. That day, unfortunately, is not today. Instead, you can use two ToolPak tools and knowledge about this type of design to provide the analysis.The levels of the Between Group variable, Media (the A variable), are in the left column.
Poisson can be a very useful tool when approaching statistical analysis with Excel. Not show how it works? Here are the steps for using Excel’s POISSON.DIST: Select a cell for POISSON.DIST’s answer. From the Statistical Functions menu, select POISSON.DIST to open its Function Arguments dialog box. In the Function Arguments dialog box, enter the appropriate values for the arguments.
You can use the worksheet function T.TEST for matched samples. Here is the Function Arguments dialog box for T.TEST along with data from a weight-loss example. The Function Arguments dialog box for T.TEST along with matched sample data.Here are the steps to follow: Enter the data for each sample into a separate data array and select a cell.
Huh? Is that correct? Two-Factor??? Without Replication?? Is this real, Excel? What’s that all about? Here's the story: If you’re looking through the data analysis tools for something like Anova: Single Factor Repeated Measures, you won’t find it. The tool you’re looking for is there, but it’s hiding out under a different name.
The worksheet function F.TEST calculates an F-ratio on the data from two samples. It doesn’t return the F-ratio. Instead, it provides the two-tailed probability of the calculated F-ratio under H0. This means that the answer is the proportion of area to the right of the F-ratio, and to the left of the reciprocal of the F-ratio (1 divided by the F-ratio).
To calculate kurtosis in Excel, you use the KURT function. The first has a peak at its center; the second is flat. The first is said to be leptokurtic. its kurtosis is positive. The second is platykurtic; its kurtosis is negative. Negative? Wait a second. How can that be? Kurtosis involves the sum of fourth powers of deviations from the mean.
Excel’s ANOVA tool does not provide a built-in facility for carrying out planned (or unplanned) comparisons among the means. With a little ingenuity, however, you can use the Excel worksheet function SUMPRODUCT to do those comparisons.The worksheet page with the ANOVA output is the launching pad for the planned comparisons.
If you’re keeping tabs on all the companies in your diversified stock portfolio, the Stock chart in Excel is the one for you. The image below shows the prices for Google stock for January 5–13, 2016. A Stock chart showing Google stock prices January 5–13, 2016.Each data point in the Stock chart is a box with a line extending upward and a line extending downward.
Visualizing a distribution often helps you understand it. It’s easy with Excel, and it’s instructive. The image below shows how to do it for a t-distribution. The function you use is T.DIST, with the FALSE option in the Cumulative box. Visualizing a t-distribution.Here are the steps: Put the degrees of freedom in a cell.
The F-distribution is extremely important in statistics and Excel gives you a great tool. In order to increase your understanding of this distribution, you need to know how to graph it. Check out the numbers and the finished product. Visualizing the F-distribution.Here are the steps: Put the degrees of freedom in cells.
Linear regression is a great tool for making predictions with Excel. When you know the slope and the intercept of the line that relates two variables, you can take a new x-value and predict a new y-value. In the example you’ve been working through, you take a SAT score and predict a GPA for a Sahutsket University student.
New to Excel 2016 (Windows only) is a capability that enables you to select a time series and with a mouse-click (several, actually) receive a set of extrapolated forecasts along with confidence intervals for each forecast.The new forecasting capability uses Excel’s new (Windows only) FORECAST FORECAST.ETS creates a forecast based on triple exponential smoothing.
Excel can help you make all sorts of calculations. Here's a selection of Excel's statistical worksheet functions. Each one returns a value into a selected cell.Check out these functions for central tendency and variability. Function What it calculates AVERAGE Mean of a set of numbers AVERAGEIF Mean of a set of numbers that meet a condition AVERAGEIFS Mean of a set of numbers that meet one or more conditions HARMEAN Harmonic mean of a set of positive numbers GEOMEAN Geometric mean of a set of positive numbers MODE.
Excel offers a wide range of statistical functions you can use to calculate a single value or an array of values in your Excel worksheets. The Excel Analysis Toolpak is an add-in that provides even more statistical analysis tools.Some Excel worksheet functions for statistical analysisExcel can help you make all sorts of calculations.
Before you can begin your statistical analysis with Excel, you need to familiarize yourself with the different types of charts you will have at your disposal. A treemap is a type of hierarchical chart that shows patterns in data. Rectangles represent tree branches, and smaller rectangles represent subbranches.
Excel knows how to help when you have more than two samples. FarKlempt Robotics, Inc., surveys its employees about their level of satisfaction with their jobs. They ask developers, managers, maintenance workers, and tech writers to rate job satisfaction on a scale of 1 (least satisfied) to 100 (most satisfied).
Excel 2016b has a variety of functions for statistical analysis. The Function Library area of the Formulas tab shows all categories of Excel worksheet functions. The steps in using a worksheet function are Type your data into a data array and select a cell for the result. Select the appropriate formula category and choose a function from its pop-up menu.
It's possible to have a relationship between two variables that's curvilinear rather than linear. The Excel function LOGEST estimates a and b for this curvilinear equation. The image below shows the LOGEST Function Arguments dialog box and the data for this example. It also shows an array for the results. Before using this function, you can attach the name x to B2:B12 and y to C2:C12.
The hypergeometric distribution deals with successes and failures and is useful for statistical analysis with Excel. Let’s start with an example. In a set of 16 light bulbs, 9 are good and 7 are defective. If you randomly select 6 light bulbs out of these 16, what's the probability that 3 of the 6 are good? Consider selecting a good light bulb as a “success.
Among the many tools that Excel provides for statistical analysis are some related to variation. Let’s take a quick look at these variation-related worksheet functions. DEVSQ DEVSQ calculates the sum of the squared deviations from the mean (without dividing by N or by N-1). For these numbers50, 47, 52, 46, and 45that's 34.
Microsoft has made a few changes to Excel’s Ribbon (the tabbed band across the top), reflecting changes in Excel. The most obvious addition is the light bulb, at the top to the right of Add-ins. It’s labeled “Tell me what you want to do.” This is called the Tell Me box, and it’s a new way to connect to Excel Help.
https://cdn.prod.website-files.com/6630d85d73068bc09c7c436c/69195ee32d5c606051d9f433_4.%20All%20For%20You.mp3

Frequently Asked Questions

No items found.