Need to run a Johnson Transformation in Excel?
QI Macros can do it for you!
Run a Johnson Transformation using QI Macros
- Select your data.
- Click on the QI Macros menu > Statistical Tools > Transformations > Johnson
- QI Macros will do the math and analysis for you.
What is Johnson Transformation?
The Johnson Transformation is a statistical technique used to normalize non-normal data by transforming it into a near-normal distribution. It’s particularly useful in Six Sigma, quality control, and statistical process control where normality is often a key assumption for various statistical tests and models.
There are (3) types of transformations, and QI Macros utilizes all (3), depending on the data:
- SB (Bounded): For data with natural bounds (e.g., percentages)
- SL (Lognormal-like): For postively skewed data with a lower bound.
- SU (Unbounded): For unbounded data with potential outliers.
Example of Johnson Transformation in Excel using QI Macros
- Select your data:
- Next, click on the QI Macros menu and choose Statistical Tools > Transformations > Johnson:
- Evaluate the Johnson Transformation results:
In this example:
- The Johnson Transformation chose to use SB (Bounded).
- You will also notice that along with the Histogram and Box Plot outputs, Confidence Intervals, Tolerance Limits, and a Probability Plot are also provided.
To calculate your transformed USL and LSL, follow the instructions below:
- Type "LSL" in cell E52 and "USL" in cell E53.
- Input "1" in cell F52 and "4" in cell F53.
- Lastly, input the following calculations:
=E$47+E$46*LN(ABS((F52-E$49)/(E$48+E$49-F52))) in cell G52
=E$47+E$46*LN(ABS((F53-E$49)/(E$48+E$49-F53))) in cell G53
NOTE: Johnson Transformation was included in the January 2025 version of QI Macros.
