Home / Statistical Tools / Analysis Tools / Johnson Transformation / How-To
How-To¶
Quick Start¶
Transform a skewed column toward normality in under two minutes.
This walkthrough uses ten cycle times with a long right tail. You will see Quantum XL pick a Johnson family, report the fitted parameters, and show the Anderson-Darling result before and after the transformation so the improvement is measurable rather than asserted.
Goal¶
Turn a right-skewed column of cycle times into a column that passes a normality test, and get a formula that converts single values in either direction.
Sample Data¶
Ten cycle times in minutes. Press Copy for Excel, then paste into a blank worksheet.
| Cycle Time |
|---|
| 3.49 |
| 6.17 |
| 3.68 |
| 3.65 |
| 4.29 |
| 4.04 |
| 3.71 |
| 5.26 |
| 6.34 |
| 3.95 |
Seven of the ten readings sit between 3.49 and 4.29, and three stretch out to 5.26, 6.17 and 6.34. That is the shape a Johnson transformation is built for: a floor the values cannot go below, and a tail on one side only.
Steps¶
-
Put the data in Excel
Press Copy for Excel above the table, click cell A1 in a blank worksheet, and press Ctrl+V. The header lands in row 1 and the ten readings in rows 2 through 11.
-
Launch the analysis
From the Excel ribbon, select QXL Stat Tools → Analysis Tools → Johnson Transformation.
-
Select your data
Select cells A1:A11, the header row plus all ten readings.
-
Choose the column
The dialog has a single Data tab and no analysis settings at all. Under Data Columns, tick Cycle Time.
Ticking the column runs the analysis immediately
This dialog closes and runs the moment you change the column selection. You do not need to click Finish, and you will usually not get the chance to. That is deliberate, and it is how the analysis dialogs work: changing the column selection commits the run. Because this dialog has no other settings, ticking a column is the only thing there is to do in it. If you want to change the selection afterwards, run the analysis again.
Result¶
Quantum XL writes one worksheet named Johnson Transformation, with a single block headed Johnson Transformation for Cycle Time.
Johnson Parameters¶
| Row | Value |
|---|---|
| Family Type | SL (Lognormal) |
| η (Eta) | 0.6703 |
| γ (Upsilon) | 0.4844 |
| λ (Lambda) | N/A |
| ε (Epsilon) | 3.4382 |
| AD p-Value | 0.735 |
| Optimal Z | 0.39 |
Lambda reads N/A, and that is correct. The SL family has no lambda; the sheet writes the text rather than leaving the cell empty. Optimal Z is not a result about your process. It is the position on the internal search grid that produced the best fit, reported so you can see which one won.
The Greek letter beside Upsilon is a gamma. The sheet labels that row γ (Upsilon), mixing the symbol and the name. It is the second Johnson parameter either way.
The two normality tables¶
| Table | Sample Size | AD Statistic | AD p-Value |
|---|---|---|---|
| Normal Fit - Original Data | 10 | 0.8828 | 0.015 |
| Normal Fit - Transformed Data | 10 | 0.2304 | 0.735 |
This is the whole point of the analysis in two rows. The original column fails a normality test at the usual 0.05 threshold; the transformed column passes it comfortably. Quantum XL prints the p-value in red when it falls below 0.05, so the first table's p-value is red and the second is not.
The data table¶
| Original Data | Transformed Data |
|---|---|
| 3.49 | -1.4998 |
| 6.17 | 1.1580 |
| 3.68 | -0.4671 |
| 3.65 | -0.5559 |
| 4.29 | 0.3769 |
| 4.04 | 0.1440 |
| 3.71 | -0.3887 |
| 5.26 | 0.8865 |
| 6.34 | 1.1985 |
| 3.95 | 0.0355 |
Rows stay in their original order, so row 4 of the output is row 4 of your data.
Transformation Formulas¶
Below the parameters, four rows let you convert single values without doing any arithmetic:
- Enter a value: a yellow input cell.
- Transformed value: a live formula that reads it.
- Enter a transformed value: a second yellow input cell.
- Original value: the reverse formula.
For this fit the forward cell holds =IF(ISNUMBER(...),0.484421+0.670265*LN(...-3.438197),""), which is the SL formula with η, γ and ε written in as numbers. Typing 5.00 into the first yellow cell returns 0.7833; typing 0.7833 into the second returns 5.00 back.
Those formulas are frozen
The parameter values are written into the formula text as literal numbers, not as references to the cells above. Editing a parameter in the Johnson Parameters table changes nothing in the formula cells. Re-run the analysis to get formulas for a different fit.
The charts¶
Two probability plots are drawn to the left of the tables, titled Johnson Transformation for Cycle Time - Original Data and - Transformed Data. The first bends away from its reference line at the top end, which is the tail; the second follows the line.
Things to try next¶
- Add a value below 3.4382 and re-run. Epsilon is the fitted floor for this family, and the SL formula takes the logarithm of the value minus epsilon. A reading at or under that floor cannot be transformed, so the fit moves to accommodate it or fails outright.
- Delete rows until only three are left. The analysis refuses with Fewer than 4 distinct values required for Johnson Transformation. The same message appears whether you have too few rows or plenty of rows carrying too few distinct values, so read it as "at least four rows and at least four different numbers".
- Run it on data that is already normal. Quantum XL asks whether you want to transform anyway. GroupBy shows exactly that, on a second machine's readings.
More How-Tos¶
- GroupBy: transform each machine, shift or supplier separately, and see what pooling them hides