Learn How to Perform a Normality Test Using Google Sheets

In the realm of statistical analysis, many powerful techniques, such as T-tests, ANOVA, and linear regression, rely on a fundamental prerequisite: the assumption that the underlying data set is normally distributed. Failing to confirm this assumption can invalidate the results of complex tests, leading to erroneous conclusions. Therefore, performing a rigorous normality test is a critical initial step in any serious data investigation.

Among the various methodologies available for testing distributional assumptions, the Jarque-Bera test stands out as a highly effective goodness-of-fit test. This test specifically evaluates whether the observed skewness and kurtosis of the sample data align sufficiently with the expected values for a theoretical normal distribution. If the sample distribution deviates too far from the expected symmetry and peakedness, we must question the normality assumption.

The Jarque-Bera test operates under the established framework of hypothesis testing. It establishes clear competing statements about the data’s distribution:

• H₀: The sample data is drawn from a population that is normally distributed.
• H_A: The sample data is not derived from a normally distributed population.

Understanding the Jarque-Bera Test Statistic

To quantify the deviation from normality, the test calculates a test statistic, denoted as JB. This statistic synthesizes the measures of asymmetry (skewness) and tail weight (kurtosis) relative to the sample size. The mathematical definition of the Jarque-Bera test statistic is crucial for understanding its implementation:

JB =(n/6) * (S² + (C²/4))

Where each component plays a specific role in assessing the sample characteristics:

• n: The total number of observations (data points) in the sample set.
• S: The sample skewness, which measures the asymmetry of the data distribution. For a perfect normal distribution, S should ideally be zero.
• C: The sample kurtosis, which measures the “peakedness” or the weight of the tails relative to the normal distribution. For the standardized normal distribution, the excess kurtosis (C) used in this formula is zero.

A key theoretical property is that, under the stringent conditions of the null hypothesis (H₀, assuming normality), the calculated JB statistic asymptotically follows a Chi-Square distribution with 2 degrees of freedom (i.e., JB ~ X²(2)).

The final step in the hypothesis testing process involves comparing the calculated p-value that corresponds to the test statistic against a predefined significance level (e.g., $alpha = 0.05$). If the resulting p-value is less than this threshold, we possess sufficient statistical evidence to reject the null hypothesis, concluding decisively that the data is not normally distributed. This comprehensive tutorial outlines the precise, step-by-step procedures required to execute this test using the robust functionality of Google Sheets.

Step 1: Preparing and Entering the Data in Google Sheets

Before initiating any complex statistical calculation, the data must be accurately prepared and imported into the spreadsheet environment. While the principles of the Jarque-Bera test apply universally, we will leverage the accessibility and collaborative nature of Google Sheets for this practical demonstration. Start by opening a new sheet and structuring the input data clearly.

For illustrative purposes, we will utilize a simulated dataset consisting of 15 distinct observations. These values are entered into a single column (Column A). It is always recommended to label the columns clearly to ensure proper identification during the formula writing stage, particularly when dealing with large datasets or complex models.

The screenshot below displays the initial data entry. Ensure that your values are entered correctly, as any transcription error can significantly impact the calculated test statistic and subsequent conclusion. We define the range of the data clearly to simplify the application of statistical functions in the next step:

Step 2: Calculating Skewness, Kurtosis, and the JB Test Statistic

The core of the Jarque-Bera test relies on measuring the sample’s skewness (S) and kurtosis (C) relative to a normal distribution. Fortunately, Google Sheets provides built-in functions that streamline these complex calculations, eliminating the need for manual summation formulas.

First, we need to calculate the necessary components: the sample size (n), the sample skewness (S), and the sample kurtosis (C). The relevant Google Sheets functions are: COUNT(A2:A16) for n, SKEW(A2:A16) for S, and KURT(A2:A16) for C. It is important to note that the KURT() function in Sheets automatically calculates the excess kurtosis (C), which is the precise measure required for the Jarque-Bera formula.

Once these three parameters are determined, we can substitute them into the main Jarque-Bera formula: JB = (n/6) * (S² + (C²/4)). The subsequent image demonstrates the precise formulas used in Column E (for display) and the resulting calculated values in Column D. We calculate the square of skewness (S²) and the square of kurtosis divided by four (C²/4) separately before combining them to derive the final statistic.

Following the execution of these formulas, the calculated JB test statistic for this specific dataset is determined to be approximately 1.0175. This numerical result now serves as the input for the final stage of determining the statistical significance.

Step 3: Calculating the P-Value for the JB Statistic

The purpose of calculating the JB statistic is not merely to obtain a number, but to determine the likelihood of observing such a statistic (or one more extreme) if the null hypothesis of normality were true. This likelihood is quantified by the p-value. As previously established, under H₀, the JB statistic follows a Chi-Square distribution with 2 degrees of freedom.

To calculate the right-tailed p-value corresponding to our test statistic in Google Sheets, we employ the dedicated statistical distribution function. Since the Jarque-Bera test is inherently an upper-tailed test (larger values of JB indicate greater deviation from normality), we must utilize the right-tailed Chi-Square distribution function, which calculates the probability of obtaining a value greater than or equal to the calculated test statistic:

=CHISQ.DIST.RT(JB test statistic, degrees of freedom)

In our scenario, where the JB statistic is 1.0175 and the degrees of freedom is 2, the formula is executed as =CHISQ.DIST.RT(1.0175, 2). The following visual confirms the practical application of this formula within the spreadsheet, yielding the final p-value required for the decision process:

The resulting p-value of the test is calculated as 0.601244. This value is the foundation of our inferential conclusion regarding the data’s distribution.

Step 4: Drawing the Statistical Conclusion

The final step in any hypothesis test involves comparing the calculated p-value against the predetermined significance level ($alpha$). Standard statistical practice dictates setting the significance threshold at $alpha = 0.05$. We recall the formal hypotheses once more to frame the decision:

• H₀: The data is normally distributed.
• H_A: The data is not normally distributed.

The decision rule is straightforward: If P-value $le alpha$, we reject H₀. If P-value $> alpha$, we fail to reject H₀.

In this analysis, our calculated P-value (0.601244) is significantly greater than the standard significance level (0.05). Consequently, we must fail to reject the null hypothesis (H₀). This crucial finding implies that the data’s observed skewness and kurtosis are not statistically different from what would be expected under a true normal distribution.

In practical terms, we lack sufficient statistical evidence to definitively conclude that the dataset is non-normal. Therefore, for the purpose of further parametric statistical modeling that requires the assumption of normality, we can confidently proceed with the assumption that the data adheres to a normal distribution.

Further Resources for Google Sheets Statistical Analysis

Mastering the Jarque-Bera test in Google Sheets is just one facet of robust statistical analysis. Google Sheets offers a powerful and flexible platform for conducting a wide array of descriptive and inferential statistics. Expanding your toolkit will allow you to handle more complex data challenges with confidence.

For those interested in exploring other essential statistical procedures directly within the spreadsheet environment, the following tutorials provide guidance on common analytical tasks: