A box-cox transformation is a commonly used method for transforming a non-normally distributed dataset into a more normally distributed one.
The basic idea is to find some value for λ such that the transformed data is as close to normally distributed as possible, using the following formula:
- y(λ) = (yλ – 1) / λ if y ≠ 0
- y(λ) = log(y) if y = 0
The following step-by-step example shows how to perform a box-cox transformation on a dataset in Excel.
Step 1: Enter the Data
First, let’s enter the values for a dataset:
Step 2: Sort the Data
Next, create an index column and a column of sorted data:
Step 3: Choose an Arbitrary Value for Lambda
Next, we’ll choose an arbitrary value of 1 for lambda and apply a temporary box-cox transformation to the data:
Step 4: Calculate the Z-Scores
Next, we’ll calculate the z-score for each value in the index:
We’ll then calculate the correlation between the box-cox transformed values and the z-scores:
Step 5: Find the Optimal Lambda Value
Next, we’ll use Goal Seek to find the optimal lambda value to use in the box-cox transformation.
To do so, click the Data tab along the top ribbon. Then click What-If-Analysis within the Forecast group.
In the dropdown menu, click Goal Seek and fill in the following values:
Once you click OK, Goal Seek will automatically find the optimal lambda value to be -0.5225.
Step 6: Perform the Box-Cox Transformation
Lastly, we’ll apply the box-cox transformation to the original data, using a lambda value of -0.5225:
Bonus: We can confirm that the transformed data is normally distributed by performing a Jarque-Bera test in Excel.
Additional Resources
How to Transform Data in Excel (Log, Square Root, Cube Root)
How to Calculate Z-Scores in Excel
when calculating the z scores, why did you subtract by .5?
Wow. Thanks for the information and sample to work with
Thanks for this informative article. The conditions in the mathematical statements introducing the Box-Cox transformation procedure need to be restated as follows;
y(λ) = (yλ – 1) / λ if λ ≠ 0
y(λ) = log(y) if λ = 0
This fixes the problem of an undefined value for log(0).
I believe there is a typo in the function definition.
The conditions should be
y(lambda) = (y^lambda-1)/lambda if LAMBDA != 0
log(y) if LAMBDA = 0
I wonder if this is easier in Excel “ToolPack”? I am using Excel 2019.
Nice work.. I tried to use your information
Thank you
What would be the equation for inverse transformation of the transformed data?
Hi Marcos…**Understanding the Inverse Box-Cox Transformation in Excel**
Hello,
The Box-Cox transformation is a power transformation that aims to stabilize variance and make the data more normally distributed. It’s widely used in statistical analyses, especially when the assumption of normality is violated.
When you’ve applied the Box-Cox transformation to your data and wish to revert to the original scale, you need to perform the inverse transformation. Below, I’ll provide the equations for both the Box-Cox transformation and its inverse, along with step-by-step instructions on how to implement the inverse transformation in Excel.
—
### **Box-Cox Transformation Formula**
Given a positive dataset \( y \) and a transformation parameter \( \lambda \), the Box-Cox transformation is defined as:
1. **When \( \lambda \neq 0 \):**
\[
y’ = \frac{y^\lambda – 1}{\lambda}
\]
2. **When \( \lambda = 0 \):**
\[
y’ = \ln(y)
\]
Where:
– \( y \) is the original data point.
– \( y’ \) is the transformed data point.
– \( \lambda \) is the transformation parameter.
– \( \ln \) denotes the natural logarithm.
—
### **Inverse Box-Cox Transformation Formula**
To revert back to the original data from the transformed data, use the inverse transformation:
1. **When \( \lambda \neq 0 \):**
\[
y = \left( \lambda \cdot y’ + 1 \right)^{1/\lambda}
\]
2. **When \( \lambda = 0 \):**
\[
y = e^{y’}
\]
Where:
– \( y’ \) is the transformed data point.
– \( y \) is the original data point.
– \( e \) is the base of the natural logarithm (approximately 2.71828).
—
### **Implementing the Inverse Transformation in Excel**
#### **Step-by-Step Guide**
**1. Identify the Lambda (\( \lambda \)) Value:**
– This is the same \( \lambda \) used during the initial Box-Cox transformation.
– Ensure you have this value available for the inverse transformation.
**2. Prepare Your Data:**
– Have your transformed data \( y’ \) in a column in Excel.
– Let’s assume the transformed data \( y’ \) is in column B, starting from cell B2.
**3. Apply the Inverse Transformation Formula:**
– **When \( \lambda \neq 0 \):**
In cell C2 (or any other cell where you want the original data \( y \)), enter the following formula:
“`excel
=POWER((λ * B2) + 1, (1/λ))
“`
Replace `λ` with the actual lambda value.
– **Example:**
If \( \lambda = 0.5 \), the formula becomes:
“`excel
=POWER((0.5 * B2) + 1, (1/0.5))
“`
Simplify \( 1/0.5 \) to 2:
“`excel
=POWER((0.5 * B2) + 1, 2)
“`
– **When \( \lambda = 0 \):**
Use the exponential function:
“`excel
=EXP(B2)
“`
**4. Drag the Formula Down:**
– Once you’ve entered the formula in the first cell, drag it down to apply it to the rest of your data.
**5. Verify the Results:**
– It’s good practice to check a few values manually to ensure the inverse transformation is working correctly.
—
### **Example**
**Assumptions:**
– Transformed data \( y’ \) is in column B (from B2 to B10).
– Lambda \( \lambda \) is 0.3.
**Formula in Excel:**
In cell C2:
“`excel
=POWER((0.3 * B2) + 1, (1/0.3))
“`
Simplify \( 1/0.3 \):
\( 1/0.3 \approx 3.333333333 \)
So the formula becomes:
“`excel
=POWER((0.3 * B2) + 1, 3.333333333)
“`
**Alternatively, you can calculate \( 1/\lambda \) in a separate cell to make the formula cleaner:**
– In cell E1, enter \( \lambda \): `0.3`
– In cell F1, calculate \( 1/\lambda \): `=1/E1`
– In cell C2, use:
“`excel
=POWER((E1 * B2) + 1, F1)
“`
This approach makes it easier to adjust \( \lambda \) without changing the formula in all cells.
—
### **Important Notes**
– **Data Positivity:** The Box-Cox transformation requires that all original data points \( y \) are positive. If your data contains zeros or negative values, consider shifting the data by adding a constant to all values before applying the transformation.
– **Lambda Determination:** The value of \( \lambda \) is typically determined through maximum likelihood estimation to best normalize the data. Ensure that you use the same \( \lambda \) for both the forward and inverse transformations.
– **Excel Functions:**
– **POWER(number, power):** Raises `number` to the specified `power`.
– **EXP(number):** Returns \( e^{\text{number}} \).
—
### **Summary**
– **Inverse Transformation when \( \lambda \neq 0 \):**
“`excel
y = ((λ * y’) + 1)^(1/λ)
“`
– **Inverse Transformation when \( \lambda = 0 \):**
“`excel
y = EXP(y’)
“`
By applying these formulas, you can accurately revert your Box-Cox transformed data back to its original scale in Excel.
—
**Feel free to ask if you need further clarification or assistance with specific data!**
Thank you!
You are very welcome Marcos! Thank you for your feedback!
You truly save my life with this article. Thanks so much!
You are very welcome, Robert!