I am pleased to announce Release 4.13 of the Real Statistics Resource Pack. The new release is now available for free download at Download Resource Pack for Excel 2007, 2010, 2013 and 2016 (Windows version) environments. Also the Examples Workbook Parts 1 and 2 and the Multivariate Examples files have been updated for compatibility with the new release.
The Real Statistics website will be updated over the course of the next few days to reflect the new capabilities in Release 4.13.
Probit Logistic Regression support
The logistic regression data analysis tool has been expanded to add an option for probit regression. This data analysis tool is now called Logistic and Probit Regression data analysis tool.
In addition, the following probit regression functions have been added, which operate like their logistic regression counterparts:
ProbitCoeff, ProbitCoeff2, ProbitCoeffs, ProbitRSquare, ProbitTest, ProbitPred, ProbitPredC
Mann-Whitney and Signed-Ranks Confidence Intervals
The following two new functions are supported which output a confidence interval for the Hodges-Lehmann median based on the Mann-Whitney and Signed-ranks tests:
MANN_CONF(R1, R2, lab, ttype, alpha): returns a column array with the following values based on the Mann-Whitney test conducted on the samples in ranges R1 and R2: lower and upper limits of the 1-alpha confidence interval using U-crit (defined by ttype), followed by the median and the lower and upper limits of the 1-alpha confidence interval based on U-crit + 1.
SRANK_CONF(R1, R2, lab, ttype, alpha): returns a column array with the following values based on the Paired Wilcoxon Signed-Ranks test conducted on the samples in ranges R1 and R2: lower and upper limits of the 1-alpha confidence interval using T-crit (defined by ttype), followed by the median and the lower and upper limits of the 1-alpha confidence interval based on T-crit + 1.
If lab = True, then an extra column containing labels is returned (default is False). alpha is the alpha value and defaults to .05.
If ttype = 0 (default), the the normal approximation is used. If ttype = 1 then a table lookup is used (via MCRIT and MPROB, or SRankCRIT and SRankPROB) with harmonic interpolation. If ttype = 2 then a table lookup is used with linear interpolation, and if ttype = 3 then an exact test is used (via MANNINV and MANNDIST, or PERMINV and PERMDIST).
There is also a one sample version of the SRANK_CONF function, namely
SRANK_CONF(R1, v, lab, ttype, alpha)
where v is the hypothetical median value.
Multinomial Logistic Regression Accuracy
A new function has been added that calculates the accuracy of a Multinomial Logistic Regression model.
MLogit_Accuracy(R1, r, lab, head, iter): returns a column array with the accuracy of the multinomial logistic regression model defined from the data in R1 for each independent variable and the total accuracy of the model. Thus, if R1 contains k independent variables, then the output is a k+1 × 1 column array (or a k+1 × 2 array if lab = True).
The arguments R1, r, lab, head and iter are as for the MLogitCoeff function.
New Distribution Functions
The following new functions support distributions that are useful in Bayesian statistics.
IGAMMA_DIST(x, alpha, beta, cum): inverse gamma distribution
IGAMMA_INV(p, alpha, beta): inverse of the inverse gamma cdf
ICHISQ_DIST(x, df, cum): inverse chi-square distribution
ICHISQ_INV(p, df): inverse of the inverse chi-square cdf
DIRICHLET_DIST(pvector, avector): Dirichlet distribution at pvector (whose values add up to 1) based on alpha parameters in avector.
DIRICHLET_RAND(avector): an array pvector consisting of random values for the Dirichlet distribution with alpha values in avector
Minor Enhancements
The CountRowsUnique function has been enhanced by adding two new optional arguments. CountRowsUnique(R1, head, ncols): returns a count of the unique rows in R1; if head = True (default False) then first row of R1 (presumably a heading) is not counted; the last ncols columns of R1 are not considered when determining uniqueness (ncols = 0).
The SUBRANGE function has been eliminated since its role is subsumed in the SUBMATRIX function.
The LogitCoeff, LogitCoeff2, LogitPred, LogitRSquare and LogitTest functions now accept non-numeric data. Rows with non-numeric data are ignored. In addition, a new optional guess argument has been added to these functions (as the last argument) which allows the user to specify initial coefficient values (instead of the default of zeros).
A new LinAlg category has been added to Excel’s standard Insert Function facility. This enables Windows users of versions of Excel 2010/2013/2016 to get additional information about the various Real Statistics matrix functions (DIAGONAL, eVECTORS, SCHUR, etc.). In addition, a new StatMath category has been added with some mathematical functions (especially those for complex number and matrix operations).
Various additional functions now accept arrays and not just ranges as their arguments.
Bug Fixes
- Fixes an error in the Kaplan-Meier confidence intervals
- Fixes an error in the VAR2_POWER and VAR2_SIZE functions
- Fixes an overflow error in the eVECTORS function
- Fixes a bug in the various logistic regression capabilities whereby LN(0) was not trapped and so an error was issued. Also fixes an error in the Multinomial Logistic Regression tool when checking whether the output will overwrite existing cells.
- Fixes a bug in LogCoeff2 which prevented it from displaying its values.
can you nest countrowsunique within an if statement? I have one column or true/false, and want to countrowsunique on another column for the true cases.
=IF(M3:M293=”true”,CountRowsUnique(G3:G293),0)
Michael,
Yes, you can nest CountRowsUnique within an IF statement. I can’t say whether the IF statement you have written will do what you need, but you can definitely use Real Statistics functions within IF statements.
Charles