Need to run a Johnson Transformation in Excel?

---

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 positively skewed data with a lower bound.
• SU (Unbounded): For unbounded data with potential outliers.

---

Example of Johnson Transformation in Excel using QI Macros

1. Select your data:



2. Next, click on the QI Macros menu and choose Statistical Tools > Transformations > Johnson:



3. 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:

1. Type "LSL" in cell E52 and "USL" in cell E53.
2. Input "1" in cell F52 and "4" in cell F53.
3. 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: If you have multiple columns of data, all rows need to be equal. If they are not, you would instead need to either run transformation on each column separately or place all data into (1) column.

*Included in the 07.2024 version of QI Macros*

---

QI Macros can draw these charts too!