Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
- Article Categories
- Book Categories
- Collections
- Custom Solutions
- Log In
- Main Menu
Article Categories
- Main Menu
Book Categories
Home
Statistical Analysis with Excel For Dummies
Author:
Published:
January 19, 2022
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.
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.
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.
Articles From The Book
3 results
Excel
Articles
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.
Excel
Articles
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.
Excel
Articles
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.
Excel
Articles
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.
Excel
Articles
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
Articles
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
Articles
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
Articles
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
Articles
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.
Excel
Articles
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.
Excel
Articles
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.
Excel
Articles
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.
Excel
Articles
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.
Excel
Articles
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.
Excel
Articles
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.
Excel
Articles
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.
Excel
Articles
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.
Excel
Articles
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).
Excel
Articles
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
Articles
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.
Excel
Articles
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.
Excel
Articles
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.
Excel
Articles
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.
Excel
Articles
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.
Excel
Articles
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
Articles
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
Articles
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.
Excel
Articles
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
Articles
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
Articles
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.
Excel
Articles
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.
Excel
Articles
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.
Excel
Articles
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.
Excel
Articles
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.