Box-Cox Regression in Excel with UNISTAT
The UNISTAT statistics add-in extends Excel with Box-Cox Regression capabilities.
For further information visit UNISTAT User's Guide section 7.2.9. Box-Cox Regression.
Here we provide a sample output from the UNISTAT Excel statistics add-in for data analysis.
Box-Cox Regression
Box-Cox Transformation: Results
Dependent Variable: Sales1
Variables Selected: Wages, Energy, Interest, Fixed Capital, Output1, Output2
Number of Cases: 55
| Value | Lower 95% | Upper 95% | |
|---|---|---|---|
| Lambda | 0.8120 | -0.0921 | 1.7859 |
Box-Cox Transformation:
y = (y ^ Lambda – 1) / Lambda
y = (POWER(y, 0.811967480789466) – 1) / 0.811967480789466
| Lambda | Chi-Square | DoF | Probability |
|---|---|---|---|
| -1 | 15.4020 | 1 | 0.0001 |
| 0 | 3.0907 | 1 | 0.0787 |
| 1 | 0.1559 | 1 | 0.6930 |
| Log of Likelihood | -238.9785 |
|---|
Normality Tests
Smaller probabilities indicate non-normality.
WARNING: There is no improvement in normality. This transformation may not be useful.
| Anderson-Darling Statistic | Probability | |
|---|---|---|
| Original Data | 1.7546 | 0.0001 |
| Transformed Data | 1.8485 | 0.0001 |
Transformed Data
| Original Data | Transformed Data | |
|---|---|---|
| 1 | 1201.9300 | 388.9108 |
| 2 | 1207.9400 | 390.4940 |
| 3 | 1239.7100 | 398.8391 |
| 4 | 1248.9100 | 401.2481 |
| 5 | 1258.7300 | 403.8158 |
| 6 | 1291.0000 | 412.2273 |
| 7 | 1298.7300 | 414.2363 |
| 8 | 1299.8400 | 414.5246 |
| 9 | 1323.6300 | 420.6925 |
| 10 | 1324.0000 | 420.7883 |
| 11 | 1441.0000 | 450.8260 |
| 12 | 1447.8600 | 452.5727 |
| 13 | 1460.4000 | 455.7615 |
| 14 | 1483.6300 | 461.6550 |
| 15 | 1504.2100 | 466.8618 |
| 16 | 1527.6300 | 472.7709 |
| 17 | 1586.0000 | 487.4247 |
| 18 | 1630.0000 | 498.4039 |
| 19 | 1673.2600 | 509.1442 |
| 20 | 1715.3900 | 519.5539 |
| 21 | 1718.3600 | 520.2859 |
| 22 | 1723.0000 | 521.4290 |
| 23 | 1724.6600 | 521.8379 |
| 24 | 1770.0000 | 532.9760 |
| 25 | 1771.5000 | 533.3436 |
| 26 | 1778.1500 | 534.9724 |
| 27 | 1802.3200 | 540.8829 |
| 28 | 1817.0200 | 544.4703 |
| 29 | 1825.5000 | 546.5373 |
| 30 | 1868.0000 | 556.8697 |
| 31 | 1870.0000 | 557.3548 |
| 32 | 1870.0400 | 557.3645 |
| 33 | 1877.9700 | 559.2871 |
| 34 | 1879.0000 | 559.5367 |
| 35 | 1883.7200 | 560.6802 |
| 36 | 1892.7300 | 562.8615 |
| 37 | 1905.5700 | 565.9667 |
| 38 | 1912.1100 | 567.5468 |
| 39 | 1935.4700 | 573.1825 |
| 40 | 1961.1100 | 579.3535 |
| 41 | 1971.9000 | 581.9459 |
| 42 | 1977.0000 | 583.1703 |
| 43 | 1991.2400 | 586.5858 |
| 44 | 1997.0400 | 587.9757 |
| 45 | 2017.1200 | 592.7816 |
| 46 | 2021.7300 | 593.8837 |
| 47 | 2022.8300 | 594.1466 |
| 48 | 2027.0000 | 595.1429 |
| 49 | 2048.0400 | 600.1644 |
| 50 | 2063.8000 | 603.9193 |
| 51 | 2064.0000 | 603.9669 |
| 52 | 2086.1200 | 609.2280 |
| 53 | 2091.8700 | 610.5939 |
| 54 | 2095.3200 | 611.4131 |
| 55 | 2106.8200 | 614.1419 |
