SolverStat is an
Excel add-in (xla) for Microsoft
Excel 97, 2000, XP, 2007, 2010, 2013, 2016 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.
SolverStat is released as Open
Source under the Educational Community License version 2.0.
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 (PDF) Download
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, 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 and Hessian Variance-Covariance Table for
Parameters
Coefficients Correlation Table;
Linssen's Modification of Beale's Measure of NonLinearity,
ObservationStatistics (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;
NewObservationStatistics
(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 Residuals,
Lilliefors Statistic for Normality of Residual,
Jarque-Bera Statistic for Normality of Residuals,
Anderson-Darling Statistic for Normality of Residuals,
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,
Models Comparison using Information Criterion,
Log Likelihood Tests for Nested Models,
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) Beale MCMC Search for Asymmetric Confidence Intervals (Graphical)
4) 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-Wilk Statistic for Normality of Residuals,
Lilliefors Statistic for Normality of Residuals,
Jarque-Bera Statistic for Normality of Residuals,
Anderson-Darling Statistic for Normality of Residuals,
D'Agostino Omnibus Statistic ,D'A. Kurtosis D'A.Skewness,
Histogram Data;
Tracks
distribution of selected simulated observations, calculated responses,
new observations.
5) 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-Wilk Statistic for Normality of Residuals,
Lilliefors Statistic for Normality of Residuals,
Jarque-Bera Statistic for Normality of Residuals,
Anderson-Darling Statistic for Normality of Residuals,
D'Agostino Omnibus Statistic ,D'A. Kurtosis D'A.Skewness,
Histogram Data;
Tracks
distribution of selected simulated observations, calculated responses,
new observations.
6) Uncertainty Propagation via IRWLS
7) Leave one out Cross Validation
8) Monte Carlo Cross Validation
9) Analysis of Power and Significance for the comparison of
two models
10) Monte Carlo Simulation of Observations Diagnostic
11) Outliers search via Robust Regression (IRLS, LTS, LMS)
12) Logistic Regression
Logistic
Regression Statistics
Log Likelihood of Fitted Model and Intercept-Only Model,
G and Deviance,
Pseudo R2, Cox & Snell R2,
Nagelkerke R2,
AIC and BIC,
Models Comparison using Information Criterion,
Hosmer-Lemeshow Goodness of Fit Test,
Contingency Table for the Hosmer-Lemeshow Test,
Hosmer-Lemeshow Classification Table,
Area under the ROC Curve,
Association
Measure Table (pairs, % concordant, % discordant, % tied, Somers' D,
Goodman-Kruskal Gamma, Kendall's Tau-a , Concordance index c),
Parameters Statistic and Wald Test,
Variance-Covariance and Correlation Table for Parameters,
Observation Diagnostic.
13) Poisson Regression
Poisson
Regression Statistics
Log Likelihood of Fitted Model and Intercept-Only
Model, Maximum, Deviance, Dispersion Phi ,
Pearson R2, Adjusted Pseudo R^2
AIC and BIC,
Models Comparison using Information Criterion,
Parameters Statistic and Wald Test,
Variance-Covariance and Correlation Table for Parameters,
Observation Diagnostic.
14) Principal Component Analysis based on Covariance or Correlation
Matrix
PCA Statistic
Descriptive Statistics: mean, variance, sd,
Correlation and Covariance Matrix,
Eigenvalues with percent, cumulative percent and the IND of Malinowsky,
Loadings (Eigenvectors),
Correlation of Principal Components with Original Variables,
Principal Component Scores from Correlation Matrix,
Residuals analysis: Hotelling T2 and Hawkins HT2,
Plots: Loadings, Scores and JK-Biplot.
15)
Box-Muller Normal Random Generator for regression simulation
16) Built-in routine for linear regression
17) Descriptive Statistic for one or more series of data:
Univariate
Statistic
Mean,
Variance,
SD,
Sample LCI and UCI,
SEM,
t Value,
Mean LCI and UCI,
SD LCI and UCI,
SEM LCI and UCI,
Median,
Percentile SD,
Percentile LCI and UCI ,
Maximum and Minimum,
Normality Test Probability: Shapiro-Wilk, Lilliefors, Anderson-Darling,
Jarque-Bera,
D'Agostino Kurtosis, Skewness, Omnibus,
Skewness, Kurtosis,
Fisher Skewness g1 Kurtosis g2,
Histogram Data.
18) Runs and Sign tests for one or more series of data
19) Monte Carlo search of global minimum of the sum of squares in
nonlinear regressions
20) Correlation tests; Pearson, Spearman, Kendall and
Variance-Covariance Matrix
21) Lin's Concordance Test
22) Univariate outliers
23) Box-Muller Normal Random Generator
24) Correlated Random Generator
25) Resampling Tool
26) Histogram tools
28) Conway-Maxwell-Poisson Regression.
29) Monte Carlo Worksheet Recording (mcmon).
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 XP, 2002, 2003, 2007, 2010, 2013, 2016 Users:
read the installation
notes.
Acknowledgements Many
thanks for suggestions, comments and bugs report to: Henk-Jan van Well,
Michael Reidy, Leigh Ward, Marcello Merli, Peter Sands, Alexander
Puziy, Tom Maes, Marcel Neitsch, John C. Pezzullo, Robert De Levie,
Richard Kennedy, Matej Klanjscek, Ali Faraj Tabar, Michel Meyer,
Kristian Murfitt, Jon
Short, Karl-Heinz Wolf, Carlos Mario Rivera, Pieter Bloembergen, John
DeLuca, Raf Schepers, Brian Coblitz, Marco Baptista, Avi Benita, and
excuse me if I forgot someone...
Research papers in which
the original SolverStat paper is cited:
Merli,
M.; Sciascia, L.; Turco Liveri, M. L.; Regression diagnostics
applied in kinetic data processing: Outlier recognition and robust
weighting procedures. INTERNATIONAL JOURNAL OF CHEMICAL KINETICS
2010(42) 587-607.
Tholander, F.; Roques, B.P.; Fournie-Zaluski, M.C.; Thunnissen, M.M.G.M. ; Haeggstrom,
J.Z.; Crystal structure of leukotriene A4 hydrolase in complex with
kelatorphan, implications for design of zinc metallopeptidase
inhibitors, FEBS LETTERS, 2010(584) 3446-3451.
Kemmer, G.; Keller, S.; Nonlinear least-squares data fitting in Excel
spreadsheets. NATURE PROTOCOLS 2010(5) 267-281.
Bloembergen, P.; On the uncertainty in the correction for the
size-of-source effect. METROLOGIA 2009(46) 544-553.
Nunes,
N.; Ventura, C.; Martins F.; Leitao; R.E.; Modeling Preferential
Solvation in Ternary Solvent Systems. JOURNAL OF PHYSICAL CHEMISTRY B
2009(113) 3071-3079.
Baratta, W.; Siega, K.; Rigo, P.;
Catalytic transfer hydrogenation with terdentate CNN ruthenium
complexes: The influence of the base. CHEMISTRY-A EUROPEAN JOURNAL
2007(13) 7479-7486.
Muhlack R.; Nordestgaard, S.; Waters,
E.J.; Deans, L.; Policki, P.; Colby, C.; In-line dosing for bentonite
fining of wine or juice: Contact time, clarification, product recovery
and sensory effects. AUSTRALIAN JOURNAL OF GRAPE AND WINE RESEARCH
2006(12) 221-234.
Del Piero, S.; Melchior, A.; Polese, P.;
Portanova, R.; Tolazzi, M.; A novel multipurpose excel tool for
equilibrium speciation based on Newton-Raphson method and on a hybrid
genetic algorithm. ANNALI DI CHIMICA 2006(96) 29-49.
Tholander,
F.; Kull, F.; Ohlson, E.; Shafqat, J.; Thunnissen, M.M.G.M.;
Haeggstrom, J.Z.; Leukotriene A(4) hydrolase, insights into the
molecular evolution by homology modeling and mutational analysis of
enzyme from Saccharomyces cerevisiae. JOURNAL OF BIOLOGICAL CHEMISTRY
2005(280) 33477-33486.
Baratta, W.; Mealli, C.; Herdtweck,
E.; Ienco, A.; Mason, S.A.; Rigo, P.; Nonclassical vs classical metal
center dot center dot center dot H3C-C interactions: Accurate
characterization of a 14-electron Ruthenium(II) system by neutron
diffraction, database analysis, solution dynamics, and DFT studies.
JOURNAL OF THE AMERICAN CHEMICAL SOCIETY 2004(126) 5549-5562.
Comuzzi,
C.; Melchior, A; Polese,P.; Oxygenation reaction of Co(trien)(2+)
complex in dimethylsulfoxide and the aerobic oxidation of
2,6-di-tert-butylphenol catalyzed by Co(II)-amine complexes. INORGANICA
CHIMICA ACTA 2003(355) 57-63.
License Solverstat 2019 R0 (4.0) August 19, 2019
Release Download
SolverStat compressed
as Zip from google.com and
open with winzip, 7-Zip or similar.
Examples August 20, 2019 Release Download
Examples compressed
as Zip from google.com and
open with winzip, 7-Zip or similar.
Manual (PROOF) version
0.1b September 10, 2010 Release The
Manual (pdf) from www.freewebs.com The
Manual (pdf)
from google.com
June 28,
2003 Release
Fixed a bug in the computation of Lack of Fit.
February
05, 2004 Release
Fixed a bug in the use of Weights<1.
December 27,
2008 Release
Released version 2.0 as open source.
Added new functionality and corrected minor bugs.
March 18,
2009 Release
Fixed an istallation problem.
September
20,
2009 Release
-
The VBA function Val() was replaced with the function Cdblx() to solve
some problem with the use of comma as decimal separator.
- Fixed a problem with the output of min and max in the descriptive
statistic routine and added a entry "Range".
-
Fixed a problem with the excel function
"Application.ChiInv(df,p)" for df>1000; in that cases, now
SolverStat uses the function df*FInv(df,1E+09).
February 24,
2010 Release
- Fixed some problems with decimal separator introduced in previous
release.
- Improved the computation speed of regression statistics (in some
cases from minutes to seconds).
- Introduced the Association Measure Table for logistic regression.
- SolverStat now can configure the Excel Solver.
- New guided procedure for logistic regression.
- Fixed a problem with FormulaLocal in Bootstrap procedure.
August 12,
2010 Release
- Fixed some minor bugs.
- Added the Poisson regression procedure.
- First release of The
Manual.
September
10, 2010 Release
- Revised outliers procedure and added the Grubb's test.
- New release of The
Manual (Proof).
August 19,
2019 Release
- Conway-Maxwell-Poisson Regression.
- Monte Carlo Worksheet Recording (mcmon).
- Minor bug fixes and improvements.