Data Transformation in Excel with UNISTAT
The UNISTAT statistics add-in extends Excel with Data Transformation capabilities.
For further information visit UNISTAT User's Guide section 9.3.7. Data Transformation.
Here we provide a sample output from the UNISTAT Excel statistics add-in for data analysis.
Data Transformation
Johnson Transformation: Results
Data variable: cm
Number of Cases: 33
| Z-statistic for best fit | 0.7200 |
|---|---|
| Gamma | 0.5500 |
| Delta | 0.6075 |
| Xi | 5.6319 |
| Lambda | 6.8365 |
Transformation selected: Johnson Bounded System (SB)
z = Gamma + Delta * LN((x – Xi) / (Xi + Lambda – x)), Xi < x < Xi + Lambda
z = 0.549976426928764 + 0.607452018062996 * LN((x – 6.83647857110731) / (5.63190833413029 + 6.83647857110731 – x))
Normality Tests
Smaller probabilities indicate non-normality.
| Anderson-Darling Statistic | Probability | |
|---|---|---|
| Original Data | 0.5988 | 0.1105 |
| Transformed Data | 0.2723 | 0.6477 |
Transformed Data
| Original Data | Transformed Data | |
|---|---|---|
| 1 | 6.9000 | -2.1675 |
| 2 | 7.0000 | -1.5821 |
| 3 | 7.0000 | -1.5821 |
| 4 | 7.2000 | -1.0741 |
| 5 | 7.2000 | -1.0741 |
| 6 | 7.3000 | -0.9149 |
| 7 | 7.3000 | -0.9149 |
| 8 | 7.4000 | -0.7843 |
| 9 | 7.6000 | -0.5754 |
| 10 | 7.6000 | -0.5754 |
| 11 | 7.7000 | -0.4880 |
| 12 | 7.8000 | -0.4086 |
| 13 | 7.9000 | -0.3354 |
| 14 | 8.6000 | 0.0728 |
| 14 | 8.7000 | 0.1222 |
| 15 | 8.8000 | 0.1703 |
| 16 | 8.8000 | 0.1703 |
| 17 | 9.1000 | 0.3085 |
| 18 | 9.1000 | 0.3085 |
| 20 | 9.2000 | 0.3531 |
| 21 | 9.3000 | 0.3971 |
| 22 | 9.3000 | 0.3971 |
| 23 | 9.4000 | 0.4408 |
| 24 | 9.8000 | 0.6137 |
| 25 | 9.9000 | 0.6571 |
| 26 | 10.1000 | 0.7447 |
| 27 | 10.1000 | 0.7447 |
| 28 | 10.4000 | 0.8804 |
| 29 | 10.5000 | 0.9273 |
| 30 | 10.9000 | 1.1283 |
| 31 | 11.5000 | 1.5048 |
| 32 | 11.7000 | 1.6709 |
| 33 | 12.1000 | 2.1654 |
Data Transformation
Box-Cox Transformation: Results
Data variable: cm
Number of Cases: 33
| Value | Lower 95% | Upper 95% | |
|---|---|---|---|
| Lambda | -0.7406 | * | 1.5260 |
Box-Cox Transformation:
y = (y ^ Lambda – 1) / Lambda
y = (POWER(y, -0.740558087800664) – 1) / -0.740558087800664
| Lambda | Chi-Square | DoF | Probability |
|---|---|---|---|
| -1 | 0.0477 | 1 | 0.8272 |
| 0 | 0.3982 | 1 | 0.5280 |
| 1 | 2.2454 | 1 | 0.1340 |
| Log of Likelihood | -11.9389 |
|---|
Normality Tests
Smaller probabilities indicate non-normality.
| Anderson-Darling Statistic | Probability | |
|---|---|---|
| Original Data | 0.5988 | 0.1105 |
| Transformed Data | 0.5901 | 0.1162 |
Transformed Data
| Original Data | Transformed Data | |
|---|---|---|
| 1 | 6.9000 | 1.0273 |
| 2 | 7.0000 | 1.0307 |
| 3 | 7.0000 | 1.0307 |
| 4 | 7.2000 | 1.0373 |
| 5 | 7.2000 | 1.0373 |
| 6 | 7.3000 | 1.0405 |
| 7 | 7.3000 | 1.0405 |
| 8 | 7.4000 | 1.0436 |
| 9 | 7.6000 | 1.0496 |
| 10 | 7.6000 | 1.0496 |
| 11 | 7.7000 | 1.0525 |
| 12 | 7.8000 | 1.0554 |
| 13 | 7.9000 | 1.0581 |
| 14 | 8.6000 | 1.0759 |
| 14 | 8.7000 | 1.0783 |
| 15 | 8.8000 | 1.0806 |
| 16 | 8.8000 | 1.0806 |
| 17 | 9.1000 | 1.0872 |
| 18 | 9.1000 | 1.0872 |
| 20 | 9.2000 | 1.0893 |
| 21 | 9.3000 | 1.0914 |
| 22 | 9.3000 | 1.0914 |
| 23 | 9.4000 | 1.0934 |
| 24 | 9.8000 | 1.1012 |
| 25 | 9.9000 | 1.1031 |
| 26 | 10.1000 | 1.1067 |
| 27 | 10.1000 | 1.1067 |
| 28 | 10.4000 | 1.1120 |
| 29 | 10.5000 | 1.1136 |
| 30 | 10.9000 | 1.1201 |
| 31 | 11.5000 | 1.1291 |
| 32 | 11.7000 | 1.1319 |
| 33 | 12.1000 | 1.1372 |
