I am pleased to announce Release 6.1 of the Real Statistics Resource Pack. The new release is now available for free download at Download Resource Pack for Excel 2010, 2013, 2016 users as well as for Excel 2013, 2019 and 365 users in both Windows and Mac environments.
The Basic, Distribution, Correlation, Nonparametric, Anova 2 and Time Series examples worksheets have also been revised for compatibility with the new release. These are available for free download at Download Examples Workbooks.
Over the course of the next few days, the website will be updated for compatibility with the new releases.
If you are getting value from the Real Statistics website or software, I would appreciate your donations to help offset the costs of the website by going to Please Donate.
The following is a summary of the new features in Release 6.1.
Chi-square Independence Test for Ordered Data
The Chi-square Test for Independence data analysis tool has been enhanced to support ordered row and column categories. This results in more accurate results when the categories are ordered (instead of categorical in the usual chi-square test of independence).
Biserial Correlation
The BCORREL function has now been enhanced to include a confidence interval and p-value (for the test that the biserial correlation is zero). BCORREL is now an array function with the following definition:
BCORREL(Rx, Ry, lab, alpha): returns a column array with the values: biserial correlation coefficient, z-stat, p-value, left and right ends of the 1–alpha confidence interval. Rx is a column array with 0 and 1 values and Ry is a matching column array with numeric values. If lab = TRUE (default FALSE), then a column of labels is appended to the output; if omitted alpha defaults to .05.
You can still use the non-array form =BCORREL(Rx, Ry) as before to calculate the biserial correlation coefficient. Note, however, that when calculating the biserial correlation coefficient, the population version of the standard deviation is used instead of the sample version. (thanks to Jari for suggesting this change)
Order Statistics
When a random sample is selected from a finite population (which can be represented a 1, 2, …, N), we might be interested in knowing the kth smallest element in the sample for any value of k. These are the order statistics. The following new functions have been added:
OrderDist(x, N, n, k) = the probability that x is the kth order statistic when a sample of size n is drawn from the population 1, 2, …, N.
MeanOrder(N, n, k) = the expected value of the kth order statistic when a sample of size n is drawn from the population 1, 2, …, N.
RangeDist(x, y, N, n) = the probability that x and y are the first and last elements, in order, from a sample of size n drawn from the population 1, 2, …, N.
MeanRange(N, n) = the expected value of y – x where x and y are the first and last elements, in order, from a sample of size n drawn from the population 1, 2, …, N.
SpreadDist(x, N, n) = the probability that the largest element minus the smallest element in the sample of size n taken from the population 1, 2, …, N is x.
(Thanks to John B for inspiring these enhancements.)
Basic Forecasting Data Analysis Tool
The Basic Forecasting data analysis tool has been enhanced as follows:
- A new Holt-Winters Additive option has been added (the existing version is Holt-Winters Multiplicative option is still available)
- Optimization of the parameters can be done based on minimizing the MAE and MAPE error statistics (as well as MSE as in the past)
- The MAPE statistic has been added to the output
- The initial trend value can now be set to a non-zero value. This is done by choosing the Initialize Trend option
Note that optimization of the forecasting parameters can be sensitive to the initial values. For this reason, often it will be advisable to use Solver’s MultiStart option as described at Solver.
Complex Matrices
The following new array functions have been added to support complex number matrices using the format Z = A + Bi where A and B are arrays of the same size and shape, both of which only contain real values. (thanks to Frank F who requested some of the new complex matrix functions)
The following array functions are supported, where Z is a complex matrix in Real Statistics format.
- ZConj(Z): returns the conjugate matrix Z’ = A – Bi
- ZIndex(Z, r, c): returns a 2 × 1 array representing the complex number z = a + bi in the rth row and cth column of Z.
Real Statistics now supports complex number matrices whose elements are strings in standard Excel complex number format (such as “3+6i”). For arrays Z, Z1 and Z2 in this form and strings z in the Excel number format, we have added the following new array functions:
- ZMADD(Z1, Z2) = Z1 + Z2
- ZMSUB(Z1, Z2) = Z1 – Z2
- ZMMULTZ1, Z2) = Z1 × Z2
- ZMMULTSCALAR(Z, z) = z * Z
- ZCONJUGATE(Z) = the conjugate of Z
- ZMINVERSE(Z) = Z-1
- ZMDETERM(Z) = the determinant of Z
Note that functions equivalent to ZIdentity, ZTranspose and ZIndex are not necessary since the standard Excel functions MUNIT, TRANSPOSE and INDEX can be used with complex arrays whose elements are in Excel complex number format. For users of versions of Excel prior to Excel 2013, the Real Statistics function Identity can be used instead of MUNIT.
Just as the existing Real Statistics CMap and CText functions can be used to translate between complex numbers in the Excel format (text) and the Real Statistics format (1 × 2 numeric array), we introduce the following two new array functions:
-
- ZMap(Z): takes an array Z whose elements are complex numbers in Excel format and outputs an array in the A+Bi format
- ZText(Z): takes an array Z in A+Bi format and returns an equivalent array
whose elements are in Excel complex number format.
The following new functions have been added that support complex numbers z in Excel format:
- IMROOTS(z, n): returns a column array with n rows containing the nth roots of the complex number represented by the string z
- IMROUND(z, n) = the string that contains the complex number represented by z where the real and imaginary parts are rounded to n decimal places.
There is also the following array version of the IMROUND function.
- ZMROUND(Z, n): returns an array equivalent to Z but with each element rounded off to n decimal places.
This new release also corrects a bug in the CMultScalar function. The new release also supports matrices that consist of a row array that is not a range.
Minor changes and bug fixes
- The output from the ICC option of the Interrater Reliability data analysis tool replaces the label ICC Case by ICC Class.
- The Mixed Anova example on the Anova 2 examples workbook had some errors, which have now been corrected. (thanks to Sun for identifying this problem)
- Note that you may get some strange results when you use cells that are date formatted as column headers. You should use the text versions of such column headings instead. (thanks to Zhivan for identifying this problem).
- Bugs in the Benjamini-Hochberg and Benjamini-Yekutieli options of the Multiple Tests data analysis tool have been fixed, esp. by reversing the rankings. (thanks to Paul N for identifying this problem)
Dr Zaionitz Good Mornig, why the last version is coming as txt? and no in Excel? Or it is a problem in my system.
Thank you
Dr buenos días, pouqe la ultima del archivo ejecutable versión no viene en Excel?
Como se descarga?
Muchas gracias
Hello Gerardo,
I just got a comment from someone with a similar problem, but when I downloaded the last version for Excel 2010/2013/2016/2019/365 (Windows) two minutes ago it worked perfectly.
Please try again, but let me know if you are still having problems.
Charles