|
New Release June, 28
2003
Clara Comuzzi,
Pierluigi Polese*,
Andrea Melchior,
Roberto Portanova,
Marilena Tolazzi.
Dipartimento
di Scienze e Tecnologie Chimiche, Università di Udine, Via Cotonificio
108, I-33100 Udine, Italy.
SolverStat is an Excel add-in (xla) for Microsoft
Excel 97, 2000, XP that performs advanced statistical tests on least squares
regression data.
In Microsoft Excel, the built-in optimization package Solver yields minimized
least squares regression parameters: the use of this utility in association
with the worksheet functions makes Excel a flexible tool to solve linear
and non linear regression problems. Nevertheless, Solver provides no estimates
of the precision of the fitted parameters and an incomplete and unprompted
analysis of fittings: the statistical analysis of the result is, on the other
hand, a crucial step to evalutate the reliability of the models.
SolverStat overcomes that limits.
Download the article in pdf format from ScienceDirect.
1) Regression statistics for linear and non linear regression, non weighted or weighted least squares, a priori (absolute) or a posteriori weighting:
Regression Statistics
Analysis of Variance: model, residual, lack of fit, pure error and total
SS, d.f., MS,
R, LCI and UCI for R, R2, R2adjusted,
Regression Coefficients Statistic: standard error, calculated z or t ,critical
z or t, LCI, UCI, Passed Probability, CV%,
F or chi2 Joint Confidence Coefficients Statistic;
Variance-Covariance Table,
Coefficients Correlation Table;
Observation Statistics (CI based on t or z Statistic)
Residuals Standard Error;
Mean LCI and UCI,
Prediction Standard Error,
Prediction LCI and UCI,
Leverage,
Standard Residuals,
PRESS Residuals,
Studentized Residuals,
Studentized Deleted Residuals,
Cook's Distance,
DFFITS,
COVRATIO,
DFBETAS,
Z Expected Under Normality for Residuals;
New Observation Statistics (CI based on t or z
Statistic)
Standard Error,
Mean LCI and UCI,
Prediction Standard Error,
Prediction LCI and UCI,
Passed Probability for Sample Mean,
Passed Probability for Single Observation;
Residual Statistics
Durbin Watson Statistics for Autocorrelation of Residuals (Bounds value,
Beta Aproximation probability, a + bDu Aproximation probability),
Box Pierce Ljung Q' Test for Autocorrelation Of High Order,
Autoregressive Order Q'Residuals Runs Test,
Shapiro Wilk Statistic for Normality of Residual,
Shapiro Francia Statistic for Normality of Residual,
Lilliefors Statistic for Normality of Residual,
Jarque-Bera Statistic for Normality of Residual,
D'Agostino Omnibus Statistic ,D'A. Kurtosis D'A.Skewness,
Modified Levene Test for Constant Variance (2 groups) and Multiple Levene
Test for Constant Variance (3 groups),
Breush-Pagan Test for Homoscedasticity of Residuals,
Koenkar-Evans NR2 Test for Homoscedasticity of Residuals;
Collinearity Diagnostics
Regressors Correlation Matrix,
Condition Number of the Regressors Correlation Matrix,
VIF and Tolerance;
Model selection
PRESS-Predictive Error Sum of Squares,
MEP-Mean Error of Prediction,
R2 prediction,
SDEC-Standard Deviation of Error of Calculations,
SDEP-Standard Deviation of Error of Predictions,
AIC-Akaike Information Criteria,
AICc-Adjusted Akaike Information Criteria,
HQC-Hannan Quinn Information Criteria,
SBC Schwarz Baynesian Information Criteria,
F Test for Models Comparison,
Extra Sum F Test for Significance of Extra Parameters.
2) F or Chi2 Search for Asymmetric Confidence Intervals:
Parameters Statistics
Individual Asymptotic Sigma,
Simultaneus Asymptotic LCI and UCI,
Simultaneus Asymmetric LCI and UCI.
3) Montecarlo simulations for Parameters Distribution:
Asymptotic Statistic
Individual Asymptotic Standard Deviation
Individual Asymptotic LCI and UCI,
Simultaneus Asymptotic LCI and UCI;
Distribution Statistics
mean of parameters distribution,
variance,
standard deviation,
Sample LCI and UCI,
standard error of mean,
t Value,
Probability Level,
mean LCI and UCI,
Median,
left percentile standard deviation,
right percentile standard deviation,
percentile LCI and UCI,
1, 2.5, 5, 10 , 90, 95, 97.5, 99 percentile,
maximum,
minimum,
Shapiro Francia Statistic for Normality of Residual,
Lilliefors Statistic for Normality of Residual,
Jarque-Bera Statistic for Normality of Residual,
D'Agostino Omnibus Statistic ,D'A. Kurtosis D'A.Skewness,
Histogram Data;
Tracks
distribution of selected simulated observations, calculated responses, new
observations.
4) Bootstrap of Observations or Weighted Residuals for Parameters Distribution:
Asymptotic Statistic
Individual Asymptotic Standard Deviation
Individual Asymptotic LCI and UCI,
Simultaneus Asymptotic LCI and UCI;
Distribution Statistics
bias,
mean of parameters distribution,
variance,
standard deviation,
Sample LCI and UCI,
standard error of mean,
t Value,
Probability Level,
mean LCI and UCI,
Median,
left percentile standard deviation,
right percentile standard deviation,
percentile LCI and UCI,
1, 2.5, 5, 10 , 90, 95, 97.5, 99 percentile,
maximum,
minimum,
Shapiro Francia Statistic for Normality of Residual,
Lilliefors Statistic for Normality of Residual,
Jarque-Bera Statistic for Normality of Residual,
D'Agostino Omnibus Statistic ,D'A. Kurtosis D'A.Skewness,
Histogram Data;
Tracks
distribution of selected simulated observations, calculated responses, new
observations.
5) Box-Muller Normal Random Generator for regression simulation.
6) Built-in routine for linear regression.
7) Descriptive Statistic for one or more series of data.
8) Runs and Sign tests for one or more series of data.
This software is free under license condition.
Important Note
It works only as Excel add-in. It was written with the italian version of Excel 97. You should not encounter any problem, but it has not been tested other localized version of Excel.
To use SOLVERSTAT, Solver package must be installed in your system. See the Tools Menu in Excel: if Solver is not installed, open the Tools/Add-ins menu; if Solver is missing, run the Office Setup.exe program with the Add/Remove option, and install the Solver add-in. Read also the Microsoft documentations.
Excel 2002, 2003 Users: read installation notes.
We will appreciate every suggestions and bug reports.
Acknowledgements
We thank Marcel Neitsch for useful discussion on Excel VBA References.
| This site is hosted for free by FreeWebs.com. Click here to get your own free website. |