NEW (January, 25 2006): EST (Equilibrium Speciation Tool) is a new Excel add-in for the simulation of chemical equilibria in solution. The program calculates the thermodynamic equilibrium concentrations of species in aqueous and non aqueous chemical systems. It handles homogeneous (complexation, acid-base, redox) and heterogeneous equilibria (solubility and distribution with other solvent or gas phase). Corrections for ionic strength can be made and activities calculated. It allows the users to simulate, or to obtain from experimental data, desired chemical-physical parameters as well as to interact with other available or freely created Excel tools. It is based on classical Newton-Raphson method applied to mass balance and improved by using a robust Genetic Algorithm.

Go to the EST Home Page

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.

Citation: "SOLVERSTAT : a new utility for multipurpose analysis. An application to the investigation of dioxygenated Co(II) complex formation in dimethylsulfoxide solution."
C.Comuzzi, P.Polese, A.Melchior, R.Portanova, M.Tolazzi. Talanta 59 (2003) 67-80.

Download the article in pdf format from ScienceDirect.

Features:

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, R², R²adjusted,
  Regression Coefficients Statistic: standard error, calculated z or t ,critical z or t, LCI, UCI, Passed Probability, CV%,
  F or chi² 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,
  R² 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 Chi² 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.

License

---

Download  SolverStat February  05, 2004 Release

Alternative version for missing reference to Solver or localized Excel

• June 28, 3003 Release
  Fixed a bug in the computation of Lack of Fit.
  Fixed a bug in the computation of Lack of Fit.
• February 05, 2004 Release
  Fixed a bug in the use of Weights<1

Installation Notes December 19, 2003

---

Download  Example  January  29, 2003  Release

solverstat@lycos.it

[ DSTC Home Page ]  [Group of Thermodynamics of Metal Complexes]

This site is hosted for free by FreeWebs.com. Click here to get your own free website.