Many statistical tests make the assumption that datasets are normally distributed.
However, this assumption is often violated in practice. One way to address this issue is to transform the values of the dataset using one of the following three transformations:
1. Log Transformation: Transform the values from y to log(y).
2. Square Root Transformation: Transform the values from y to √y.
3. Cube Root Transformation: Transform the values from y to y1/3.
By performing these transformations, the data typically becomes closer to normally distributed. The following examples show how to perform these transformations in Excel.
Log Transformation in Excel
To apply a log transformation to a dataset in Excel, we can use the =LOG10() function.
The following screenshot shows how to apply a log transformation to a dataset in Excel:
To determine if this transformation made the dataset more normally distributed, we can perform a Jarque-Bera normality test in Excel.
The test statistic for this test is defined as:
JB =(n/6) * (S2 + (C2/4))
where:
- n: the number of observations in the sample
- S: the sample skewness
- C: the sample kurtosis
Under the null hypothesis of normality, JB ~ X2(2).
If the p-value that corresponds to the test statistic is less than some significance level (e.g. α = .05), then we can reject the null hypothesis and conclude that the data is not normally distributed.
The following screenshot shows how to perform a Jarque-Bera test for the raw data and the transformed data:
Notice that the p-value for the raw data is less than .05, which indicates that it is not normally distributed.
However, the p-value for the transformed data is not less than .05, so we can assume that it is normally distributed. This tells us that the log transformation worked.
Square Root Transformation in Excel
To apply a square root transformation to a dataset in Excel, we can use the =SQRT() function.
The following screenshot shows how to apply a square root transformation to a dataset in Excel:
Notice that the p-value of the Jarque-Bera normality test for the transformed data is not less than .05, which indicates that the square root transformation was effective.
Cube Root Transformation in Excel
To apply a cube root transformation to a dataset in Excel, we can use the =DATA^(1/3) function.
The following screenshot shows how to apply a cube root transformation to a dataset in Excel:
The p-value of the Jarque-Bera normality test for the transformed data is not less than .05, which indicates that the cube root transformation was effective.
All three data transformations effectively made the raw data more normally distributed.
Out of the three transformations, the log transformation resulted in the largest p-value in the Jarque-Bera normality test, which tells us that it likely made the data the “most” normally distributed out of the three transformation methods.
Additional Resources
How to Perform a Box-Cox Transformation in Excel
What is the Assumption of Normality in Statistics?