Table of Contents
Many statistical tests make the assumption that datasets are .
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 .
The test statistic for this test is defined as:
JB =(n/6) * (S2 + (C2/4))
where:
- n: the number of in the sample
- S: the sample skewness
- C: the sample kurtosis
Under the null hypothesis of normality, JB ~ X2(2).
If the 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.
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.