This guide will explain how to perform a normality test in Excel.
We can use the normality test to determine whether a certain dataset follows the normal distribution.
Testing normality typically requires the statistician to reject the null hypothesis that states that the dataset is not normally distributed. If the result of the normality test has a high p-value, then we have enough evidence to say that our sample follows a normal distribution.
One way to determine whether sample data follows a normal distribution is through the Jarque-Bera test (JB). The JB test looks at the skewness and kurtosis of the sample as a basis for normality. If the result of the test statistic is close to 0, this indicates that the sample follows a normal distribution.
Let’s look into a quick example of a scenario where you may need to conduct a normality test.
Suppose you want to know whether the heights of trees in a forest follows a normal distribution. After a few days of surveying, you collected 100 sample tree heights.
Given your sample dataset of 100 heights, can we determine whether the population is normally distributed?
We can perform the Jarque-Bera normality test on this sample by calculating the dataset’s skewness and kurtosis.
Kurtosis refers to the sharpness of the peak of a distribution. We can obtain the kurtosis of a dataset through the KURT function.
Skewness is the measurement of distortion in a distribution. It is also known as a measurement of asymmetry. We can find the skewness of a dataset through the SKEW function.
We can plug these values into the JB test equation to find the positive test statistic. Afterwards, we’ll perform a chi-square test to determine whether the test statistic’s p-value is enough to reject the null hypothesis.
Now that we know when to perform a normality testfunction, let’s learn how to use it and work on an actual sample spreadsheet.
A Real Example of Performing the Normality Test in Excel
The following section provides several examples of how to perform a normality test. We will also explain the formulas and tools used in these examples.
First, let’s take a look at our sample dataset. We have an Excel table with fifteen randomly-selected values from a larger population.
We’ll perform the Jarque-Bera test to determine whether the population follows the normal distribution.
We must first determine the sample size. For larger datasets, we can use the COUNT function on the sample data’s cell range.
Next, we’ll calculate the skewness and kurtosis of the given sample. We’ll use the SKEW and KURT functions to get these statistics quickly.
We’ll use a custom formula to compute the Jarque-Bera Test score:
=(D1/6) * ((D2^2) + ( (D3)^2)/4)
After finding the test score, we must compute the result’s p-value. A p-score lower than 0.05 is enough evidence to claim that the population does not follow a normal distribution.
Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below.
If you’re ready to try performing a normality test in Excel, head over to the next section to read our step-by-step breakdown on how to do it!
How to Perform a Normality Test in Excel
This section will guide you through each step needed to perform a normality test in Excel. You’ll learn how to find the skewness and kurtosis values of a given sample. We’ll use these values to perform a JB test to check if the sample suggests a normal distribution.
Follow these steps to learn how to perform a normality test in Excel:
- First, use the
COUNTfunction to determine the sample size.
- Next, use the
SKEWfunction to find the skewness of the current sample.
- We’ll use the
KURTfunction to find the sample’s kurtosis value.
- We’ll use the formula
=(D1/6) * ((D2^2) + ( (D3)^2)/4)to calculate the Jarque-Bera test score.
- We’ll determine whether to reject the null hypothesis by checking the result’s p-value. We can use the
CHISQ.DIST.RTfunction to find this p-value.
These are all the steps needed to perform a normality test in Excel.
This step-by-step guide should provide you with all the information you need to begin performing a normality test in Excel.
We’ve shown you how to perform a JB test on a dataset by finding the skewness and kurtosis of a sample. We’ve also explained each formula you need to find these values.
The normality test is just one example of the many statistical methods you can use in your spreadsheets. Our website offers hundreds of other functions and methods to help you get more out of Microsoft Excel.
With so many other Excel functions available, you can find one appropriate for your use case.
Don’t miss out on our team’s new spreadsheet tips, tricks, and best practices. Subscribe to our newsletter to stay updated on the latest guides from us!
