I am pleased to announce Release 7.2 of the Real Statistics Resource Pack. The new release is now available for free download at Download Resource Pack for Excel 2010, 2013, 2016, 2019 and 365 in both Windows and Mac environments.
Over the course of the next several days, the website will be updated for compatibility with the new release, as well as the Basics, Distributions, Non-parametric 2 and Time Series examples workbooks. (Note too that the Anova 1, Non-parametric 1, Non-parametric 2, Distributions and Time Series examples workbooks were updated in June).
If you are getting value from the Real Statistics website or software, I would appreciate your donation to help offset the costs of the website by going to Please Donate.
The following is an overview of the new features in Release 7.2:
Nominal-Ordinal Chi-square Test
The Real Statistics Chi-square Test for Independence data analysis tool has been enhanced with the addition of support for nominal-ordinal contingency tables. This data analysis tool already supports nominal-nominal tables (the traditional chi-square test) as well as ordinal-ordinal tables.
A bug in this data analysis tool has also been corrected in the case where the Standard format option is selected.
QR Factorization Enhancements
The QRFactor and QRFactorR functions have been enhanced to support a larger variety of matrices, including cases where the matrix doesn’t have full rank.
A new QRFactorQ function has also been added that calculates the Q matrix. This function takes the form QRFactorQ(R1, prec) and returns the m × n Q array for the QR factorization of the m × n matrix in R1 (note that R is an n × n matrix). Here, any values ≤ prec are treated as zero (default prec = 0). QRFactor and QRFactorR have also been enhanced to take a prec value.
The above functions calculate the reduced QR factorization. There is another version of the QR factorization A = QR where A and R are m × n matrices and Q is an m × m matrix. This factorization is supported by the new functions QRFull, QRFullQ and QRFullR.
Matrix Enhancements
The existing eVECT function, that computes the eigenvalues and eigenvectors of a square matrix, and the SCHUR function, that calculates the Schur decomposition of a matrix, have also been enhanced to use the new version of the QR factorization.
The QR Factorization, Schur Factorization and Eigenpairs (non-sym) options of the Matrix Operations data analysis tool have also been enhanced to support an enlarged class of matrices.
Fitting a Weibull distribution with multi-censored data
Real Statistics already supports the fitting of data to a Weibull distribution where m+n components enter into service at time 0 and n components fail at various times prior to time t, leaving m components still functioning at time t.
Real Statistics now supports the case where n components fail after x1, …, xn units of time and m components stay in service for (at least) y1, …, ym units of time. In this scenario, the components don’t all need to go into service at the same time. Also, some of the components can be removed from service at various times without failing.
The following function has been added to support fitting data to a Weibull distribution with this type of censoring:
WEIBULL_MCFIT(R1, R2, lab, iter, bguess, viter)
Here, R1 is a column array containing the x1, …, xn values, R2 is a two-column array in the form of a frequency table containing the y1, …, ym values. lab, iter and bguess are as for the existing WEIBULL_FIT function. viter is the number of iterations used to calculate, by simulation, the variance of the data (including censored values). viter defaults to zero (meaning that this variance is not calculated).
The WEIBULL_FIT function has now been enhanced with the addition of a viter argument as well (previously a value of 20,000 was automatically used).
The following new functions have also been added:
WEIBULL_MCMEAN(R1,R2, alpha, beta)
WEIBULL_MCVAR(R1, R2, alpha, beta, viter)
Here, R1 and R2 are as for the WEIBULL_MCFIT function and alpha and beta are the parameters of the fitted Weibull distribution. These functions are similar to the existing WEIBULL_CMEAN and WEIBULL_CVAR functions.
Prediction Intervals for Forecasts
Standard errors and prediction intervals have been added to the output from the Basic Forecasting data analysis tool. In particular, this has been done for the Exponential Smoothing, Holt’s Trend and Holt-Winters (additive) options. The Simple Moving Average and Weighted Moving Average options already provide standard errors and prediction intervals.
Enhanced FREQ2RAW function
The existing FREQ2RAW(R1) function takes the data in the two-column frequency table in R1 and outputs the raw data equivalent. This function has been enhanced to support frequency tables with more than two columns. If R1 has n columns then the first n-1 columns represent the values (i.e. 1 × n-1 vectors) and the last column represents the corresponding counts. The output is an array with n-1 columns.
New functions for compatibility with Excel 365
To provide better support for dynamic arrays, a capability that has recently been introduced in Excel 365, the following new array functions have been added that are similar to existing array functions: SortsRowsCount, SortsRowsSum, SortsRowsUnique.
In addition, the DELROWS(R1, head, blank) function has been added that is similar to DELROWBLANK when blank = TRUE and similar to DELROWNonNum when blank = FALSE (default).
New reformatting functions
In the following figure, the new array formula =IndexedValues(A2:D10) inserted in range F2:J6 is used to convert the data on the left side of the figure to the format on the right side of the figure.
The second argument in the IndexedValues(R1, type) function takes the values 0 for sum (default), 1 for use last match or 2 for use last match but if there are multiple matches then the value “dupe” is returned.
The formula =IndexedValues(A2:D10,1) returns the same output as shown in the figure above, except that cell H3 contains the value 30 (instead of 110, which is 80+30) and all the zero values are replaced by blanks. The formula =IndexedValues(A2:D10,2) returns the same output except that cell H3 now contains the value “dupe”.
The following related formula has also been added: IndexedCount(R1). E.g. the formula =IndexedCount(A2:C10) returns the same output as shown in the figure above except that all the non-zero values are replaced by 1 with the exception of cell H3, which contains the value 2 (since there are two matches). Note that column D is not used.
Embedding one function in another
Most, but not all, Excel functions can use the output from an array formula as an argument. E.g. in the formula MDETERM(MMULT(R1,R2)), which calculates the determinant of the product of the matrices in R1 and R2, the MMULT array formula is embedded inside the MDETERM function.
Quite a few Real Statistics functions only accept a range as an argument and so won’t allow another array formula to be embedded. Over time, many such Real Statistics functions are being modified to allow such embedding. In this release, the following functions have been modified in this way:
FRIEDMAN, WELCH_TEST, RANK_SUM, RANK_COMBINED, LEVENE, FKTEST, ICC, COV, COVP, CORR, PCORR, PCORREL, CRONALPHA, CALPHA, Ancova1Reg, Ancova1RegFull, Hotellingdf1, Hotellingdf2, HotellingF, HotellingT2, Hotelling, T2TEST, SSW, dfW. MSW, dfBet, SSBet, MSBet, SSWF, dfWF, MSWF, SSRow, dfRow, MSRow, SSCol, dfCol, MSCol, SSInt, dfInt, MSInt, SSTot, dfTot, MSTot, ANOVA1, ANOVARow, ANOVACol, ANOVAInt, ATEST, ATESTRow, ATESTCol, ATESTInt, AnovaStd1, StdAnova1
Minor Changes
- The continuity correction factor for the McNemar test in the COCHRAN function has been changed from 0.5 to 1.
- The output from the StdAnova1 function no longer returns lots of blank rows.
Bug Fixes
- Fixed a bug in the WINSORIZE function when two percentage arguments are used
- Fixed errors in the One-sample Proportion Test data analysis tool
Website Enhancements
A new Equivalence Testing (TOST) webpage has been added to the Real Statistics website that shows how to perform the two one-sided t-test (TOST).