Nonlinear Regression in Excel with Unistat
The Unistat statistics add-in extends Excel with Nonlinear Regression capabilities.
Further documentation is available at section 7.2.4 of the manual, Nonlinear Regression.
Sample output
Here we provide annotated example report output from the Unistat Excel statistics add-in for data analysis.
Nonlinear Regression
Regression Results
EXAMPLE 2: BIPHASIC EXPONENTIAL DECAY
Dependent Variable: ACTIVITY
Convergence achieved at iteration 11, No of halving = 0
Goodness of Fit: OK
| Parameter | Minimum | Maximum | Estimate | Standard Error | Status | Lower 95% | Upper 95% |
|---|
| INIT1 | | | 14.0304 | 1.5996 | Free | 10.6554 | 17.4054 |
|---|
| RATE1 | | | -0.2627 | 0.0320 | Free | -0.3303 | -0.1952 |
|---|
| INIT2 | | | 7.4943 | 0.1836 | Free | 7.1071 | 7.8816 |
|---|
| RATE2 | | | -0.0034 | 0.0002 | Free | -0.0039 | -0.0029 |
|---|
| With normalised weights: |
|---|
| Residual Sum of Squares | 1.4317 |
|---|
| Root Mean Square Error | 0.2902 |
|---|
| With absolute weights: |
|---|
| Residual Sum of Squares | 40.0617 |
|---|
| Root Mean Square Error | 1.5351 |
|---|
| |
| Number of Parameters | 4 |
|---|
| No of Active Parameters | 4 |
|---|
| Number of Cases | 21 |
|---|
| Degrees of Freedom | 17 |
|---|
| R-squared | 0.9863 |
|---|
| Durbin-Watson Statistic | 0.8836 |
|---|
ANOVA of Regression
| Due To | Sum of Squares | DoF | Mean Square | F-Stat | Signif |
|---|
| Regression | 71.009 | 3 | 23.670 | 281.046 | 0.0000 |
|---|
| Error | 1.432 | 17 | 0.084 | | |
|---|
| Total | 72.441 | 20 | 3.622 | | |
|---|
Correlation Matrix of Regression Coefficients
| 1 | 2 | 3 | 4 |
|---|
| 1 | 1.0000 | -0.8176 | 0.1491 | -0.1165 |
|---|
| 2 | -0.8176 | 1.0000 | -0.4890 | 0.4037 |
|---|
| 3 | 0.1491 | -0.4890 | 1.0000 | -0.8597 |
|---|
| 4 | -0.1165 | 0.4037 | -0.8597 | 1.0000 |
|---|
Covariance Matrix of Regression Coefficients
| 1 | 2 | 3 | 4 |
|---|
| 1 | 2.5589 | -0.0419 | 0.0438 | -0.0000 |
|---|
| 2 | -0.0419 | 0.0010 | -0.0029 | 0.0000 |
|---|
| 3 | 0.0438 | -0.0029 | 0.0337 | -0.0000 |
|---|
| 4 | -0.0000 | 0.0000 | -0.0000 | 0.0000 |
|---|
Actual and Fitted Values
| Row | Actual * | Fitted + | 4.0736 16.2034 |
|---|
| 1 | 16.2034 | 15.7396 | + * |
|---|
| 2 | 11.6254 | 12.2986 | * + |
|---|
| 3 | 10.2491 | 10.2439 | * |
|---|
| 4 | 9.1293 | 9.0089 | +* |
|---|
| 5 | 7.9613 | 8.2587 | *+ |
|---|
| 6 | 8.3625 | 7.3957 | + * |
|---|
| 7 | 7.4554 | 7.0768 | +* |
|---|
| 8 | 7.0782 | 6.9056 | +* |
|---|
| 9 | 6.9105 | 6.7756 | +* |
|---|
| 10 | 6.2450 | 6.5452 | *+ |
|---|
| 11 | 6.2566 | 6.3269 | * |
|---|
| 12 | 5.8914 | 6.1162 | *+ |
|---|
| 13 | 5.6744 | 5.9125 | *+ |
|---|
| 14 | 5.4640 | 5.7156 | *+ |
|---|
| 15 | 5.3833 | 5.5253 | *+ |
|---|
| 16 | 4.8935 | 5.1634 | *+ |
|---|
| 17 | 4.8322 | 4.8252 | * |
|---|
| 18 | 4.4895 | 4.5092 | * |
|---|
| 19 | 4.4750 | 4.3591 | +* |
|---|
| 20 | 4.3560 | 4.2139 | * |
|---|
| 21 | 4.2515 | 4.0736 | +* |
|---|
Residuals
| Row | Residuals | -0.9668 0.9668 |
|---|
| 1 | 0.4638 | * |
|---|
| 2 | -0.6732 | * |
|---|
| 3 | 0.0052 | * |
|---|
| 4 | 0.1204 | * |
|---|
| 5 | -0.2974 | * |
|---|
| 6 | 0.9668 | * |
|---|
| 7 | 0.3786 | * |
|---|
| 8 | 0.1726 | * |
|---|
| 9 | 0.1349 | * |
|---|
| 10 | -0.3002 | * |
|---|
| 11 | -0.0703 | * |
|---|
| 12 | -0.2248 | * |
|---|
| 13 | -0.2381 | * |
|---|
| 14 | -0.2516 | * |
|---|
| 15 | -0.1420 | * |
|---|
| 16 | -0.2699 | * |
|---|
| 17 | 0.0070 | * |
|---|
| 18 | -0.0197 | * |
|---|
| 19 | 0.1159 | * |
|---|
| 20 | 0.1421 | * |
|---|
| 21 | 0.1779 | * |
|---|
Confidence Intervals for Actual Y Values
| Row | Fitted Y | Standard Error | 95% lb Actual Y | 95% ub Actual Y |
|---|
| 1 | 15.7396 | 0.5249 | 10.4908 | 20.9883 |
|---|
| 2 | 12.2986 | 0.4407 | 7.8918 | 16.7054 |
|---|
| 3 | 10.2439 | 0.3547 | 6.6965 | 13.7912 |
|---|
| 4 | 9.0089 | 0.2710 | 6.2985 | 11.7192 |
|---|
| 5 | 8.2587 | 0.2003 | 6.2561 | 10.2614 |
|---|
| 6 | 7.3957 | 0.0948 | 6.4473 | 8.3441 |
|---|
| 7 | 7.0768 | 0.0626 | 6.4510 | 7.7025 |
|---|
| 8 | 6.9056 | 0.0567 | 6.3387 | 7.4726 |
|---|
| 9 | 6.7756 | 0.0556 | 6.2192 | 7.3320 |
|---|
| 10 | 6.5452 | 0.0550 | 5.9952 | 7.0952 |
|---|
| 11 | 6.3269 | 0.0547 | 5.7796 | 6.8742 |
|---|
| 12 | 6.1162 | 0.0547 | 5.5693 | 6.6631 |
|---|
| 13 | 5.9125 | 0.0548 | 5.3641 | 6.4609 |
|---|
| 14 | 5.7156 | 0.0551 | 5.1643 | 6.2669 |
|---|
| 15 | 5.5253 | 0.0555 | 4.9700 | 6.0805 |
|---|
| 16 | 5.1634 | 0.0565 | 4.5986 | 5.7282 |
|---|
| 17 | 4.8252 | 0.0575 | 4.2501 | 5.4004 |
|---|
| 18 | 4.5092 | 0.0585 | 3.9243 | 5.0941 |
|---|
| 19 | 4.3591 | 0.0589 | 3.7698 | 4.9483 |
|---|
| 20 | 4.2139 | 0.0593 | 3.6208 | 4.8071 |
|---|
| 21 | 4.0736 | 0.0597 | 3.4770 | 4.6702 |
|---|
Interpolation
| Variable | Value |
|---|
| TIME | 0.0000 |
|---|
| WEIGHT | 0.0000 |
|---|
| Fitted Y | 21.5247 |
|---|
