I am pleased to announce Release 4.7 of the Real Statistics Resource Pack. The new release is now available for free download (Download Resource Pack) for Excel 2010, 2013 and 2016 (Windows version) environments. The Excel 2007 version will be available later today.
The spreadsheets for all the examples used on the Real Statistics website, including those related to the new Release 4.7 features) are available for free download (Download Examples Workbooks). These are contained in three Excel files (i.e. workbooks): Examples Workbook Part 1, Examples Workbook Part 2 and Multivariate Examples. See Workbook Examples for a description of which examples are contained in which files.
In addition, there is now a new Time Series Examples file which contains the time series examples. This file is still being revised as I add new webpages to the website related to time series analysis. This process is still ongoing and I hope to complete the addition of webpages related to Release 4.6 and 4.7 shortly.
Release 4.7 contains the following new features:
Enhancement to ARIMA data analysis tool
A new Reformat for Linear Regression option has been added to the ARIMA data analysis tool which can be used to generate the standard least squares model for AR(p) processes.
In addition, the following array function has been added where R1 is an n × 1 column range containing time series data:
ARMap(R1, p): outputs an n–p × p+1 range which contains X and Y data equivalent to the data in R1 in order to perform multiple linear regression
Minor changes and enhancements
The WEIBULL_INV function has been revised for consistency with the standard Excel functions WEIBULL and WEIBULL.DIST. In particular, the inverse of WEIBULL(x,b,a,TRUE) or WEIBULL.DIST(x,b,a,TRUE) is now WEIBULL_INV(p,b,a). Previously, this was expressed as WEIBULL_INV(p,a,b).
Previously the VIF value for the first variable in the output of the Linear Regression data analysis tool was always one. This has now been changed to its actual VIF value. The same change has been made to the formula VIF(R1,1).
Changes in handling array arguments
I am in the process of changing most of the Real Statistics functions which take a range as an argument so that they can also take an array as an argument. This is already true of most standard Excel functions. E.g. if R1 is a range then =SUM(R1) is the sum of all the elements in R1. You can also use expressions such as =SUM(R1^2) or SUM(ABS(R1)) to get the sum of the squared values in R1 or the sum of the absolute values in R1. Note that these are array formulas and so you need to press Ctrl-Shft-Enter instead of just Enter.
A notable exception are the functions RANK and RANK.AVG. A formula of form RANK(x,R1) calculates the rank of x among the data elements in R1, but expressions like RANK(ABS(R1)) yield an error.
Many Real Statistics functions already allow for array arguments, but over time I will be expanding the number that can take array values instead of just ranges.
Bonferroni and Dunn-Sidak corrections
When using contrasts as part of a post hoc analysis, you need to revise the alpha value by dividing by the number of contrasts that you will make. This is called the Bonferroni correction. With k groups there are C(k,2) possible comparisons, and so if you expect to make all these comparisons then you need to divide alpha by C(k,2). It turns out that for theoretical reasons, if you limit yourself to orthogonal contrasts, you will need to make at most k – 1 contrasts, and so you can use a corrected alpha value of α/(k-1).
In the ANOVA data analysis tools which includes a Bonferroni correction option, the corrected version of alpha will always be based on division by k-1. If you use fewer or more than this number of contrasts you will need to change the corrected value of alpha manually.
The Dunn-Sidak correction works similarly, except that this time the corrected value of alpha is alpha will be set by default to 1–(1–α)1/m. where m = the number of contrasts used. In the ANOVA data analysis tools which includes a Dunn-Sidak correction option, the corrected version of alpha will be set by default to 1–(1–α)1/m. where m = k -1 If you use fewer or more than this number of contrasts you will need to change the corrected value of alpha manually.
Note that this approach has now been applied to the Contrasts option of the One Factor Repeated Measures ANOVA data analysis tool.
Bug Fixes
- Corrects a bug in the SCORREL and FKTEST function for data which contains a zero value.
- Corrects some corrupted values in the DCRIT, LCRIT and RhoCRIT lookup functions.
- Corrects an error in the calculation of the standard error for Dunn‘s post hoc test to the Kruskal-Wallis test.
- Corrects an overflow error in calculating the sample size for Cronbach’s alpha (in CALPHA_SIZE and in the Cronbach’s Alpha data analysis tool).
Complex Numbers
Real Statistics has added new support for complex numbers. A complex number is represented as a range with one row and two columns, where the first cell contains the real part of the complex number and the second cell contains the imaginary part. The following functions are supported, where z1 and z2 are complex numbers:
- CReal(z1) = real part of z1
- CImag(z1) = imaginary part of z1
- CAbs(z1) = absolute value of z1
- CConj(z1) = conjugate of z1
- CAdd(z1, z2) = z1 + z2
- CSub(z1, z2) = z1 – z2
- CMult(z1, z2) = z1 * z2
- CDiv(z1, z2) = z1 / z2
- CPower(z1, n) = z1n
Complex Matrix Operations
We represent an m × n matrix of complex numbers by a m × 2n range in Excel. This range consists of an m × n range on the left and another m × n range on the right. The first of these ranges represents the real parts of the values of the complex numbers in the complex number matrix and the second of these ranges represents the imaginary parts of the complex numbers in the complex number matrix.
This is equivalent to representing a complex matrix as A + Bi where A and B both contain only real values.
The following array functions are supported, where Z1 and Z2 are complex matrices, z1 is a complex number and n is a positive integer:
- ZAdd(Z1, Z2) = Z1 + Z2
- ZSub(Z1, Z2) = Z1 – Z2
- ZMult(Z1, Z2) = Z1 × Z2
- ZMultScalar(Z1, z1) = z1 * Z1
- ZInverse(Z1) = Z1-1
- ZTranspose(Z1) = Z1T
- ZIdentity(n) = n × n complex identity matrix (represented as an n × 2n range)