I am pleased to announce Release 5.9 of the Real Statistics Resource Pack. The new release is now available for free download at Download Resource Pack for Excel 2007, 2010, 2013, 2016 (Windows and Mac) and 2019 (Windows and Mac) environments.
The examples workbooks have been updated for compatibility with the new release. New versions of the Basics, Anova 2, Regression 1 and Regression 2 workbooks are now available.
Over the course of the next several days, the website will be updated for compatibility with the new release.
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.
My apologies to Mac users since I was unable to furnish Release 5.8 for the Mac environment. In any case, all the Release 5.8 features are included in Release 5.9 for Mac, which is available today.
The following is a summary of the new features in Release 5.9.
Mediation Analysis data analysis tool
A new Mediation Analysis data analysis tool has been added. This tool models a hypothetical causal sequence in which variable X affects outcome Y indirectly through mediator variable M, and tests whether variable M indeed mediates the relationship between X and Y.
Changes to Weighted Linear Regression data analysis tool
The Weighted Linear Regression data analysis tool has been enhanced to add a new weighted linear regression model based on regression with an intercept (in addition to the current approach using regression through the origin). Thanks to Luke for bringing up this issue.
The following new function has also been added:
WVIF(R1, R2, j) = variance inflation factor (VIF) of the jth variable for the X data in range R1 based on weighted regression using the weights in range R2.
If the third argument in WVIF is omitted then a column array with the VIF values for all the independent variables is returned.
Note too that the existing VIF(R1, j) function has also been enhanced so that when the second argument is omitted, a column array with the VIF values for all the independent variables is returned.
New SEQ function and support for Excel 2019
Microsoft has announced Excel 2019 for Windows and Mac environments. Real Statistics can be used in these environments, and over time new relevant features will be added to support Excel 2019 and to offer some of these Excel 2019 capabilities to users of Excel prior to Excel 2019.
One of the new functions, SEQUENCE, provides the ability to create an array with a sequence of values: e.g. =SEQUENCE(2,3,5,4) outputs an array with 2 rows and 3 columns; the first row contains the values 5, 9, 13 and the second row contains the values 17, 21, 25.
The new Real Statistics array function SEQ provides similar capabilities (even for users of Excel prior to Excel 2019). In addition, if the first two arguments are omitted, then the dimensions of the highlighted range are used.
Fix for Real Statistics disappearing from the ribbon
When Real Statistics is used for the first time, the data analysis tools can be accessed from the Add-Ins ribbon. This support disappears once Excel is closed, and so previously you then had to access the data analysis tools via the Ctrl-m key sequence. Alternatively, you could restore Real Statistics to the Add-Ins ribbon by pressing Alt-TI (at least in the Windows version) unchecking RealStats on the Add-Ins dialog box that appears and pressing the OK button, and then pressing Alt-TI again and checking RealStats.
You now have the following three new approaches when Real Statistics disappears from the Add-Ins ribbon (or more commonly when the Add-Ins ribbon itself disappears):
- Press the key sequence Ctrl-Shft-m
- Run the RSRibbon macro. To do this press the Alt-F8 key sequence in Windows or Option-F8 on the Mac, enter the RSRibbon macro name (on the Macro dialog box) and press the Run button.
- Press Ctrl-m and choose the Restore Real Statistics on Add-Ins Ribbon option
Termination of Real Statistics main menu
Until now, when one of the Real Statistics data analysis tools has completed its task, the user is prompted to choose another data analysis tool. Whether or not to prompt for another data analysis tool or terminate is now configurable.
Starting with this release, the default will be to terminate after one data analysis tool has completed execution (although the Color Assignment and Statistical Power and Sample Size data analysis tools are exceptions).
If you always want to see a prompt for another data analysis tool (as has been done until now), then you need to click on the Config button on the main dialog box and then check the Return to main menu option on the dialog box that appears. You can click on the Config button to change this option at any time in the future.
Enhancement to the RANDOM function
Real Statistics already offers the RANDOM function to provide a non-volatile version of the Excel worksheet functions RAND and RANDBETWEEN. This function has now been enhanced to provide a seed, so that users can generate a repeatable sequence of pseudo-random values. The new form of the the RANDOM function is:
RANDOM(a, b, FALSE, seed) = random number between a and b; i.e a non-volatile version of a + (b − a) * RAND()
RANDOM(a, b, TRUE, seed) = random integer between a and b, inclusive; i.e. a non-volatile version of RANDBETWEEN(a, b)
If a is omitted it defaults to 0, if b is omitted it defaults to 1 and if the third argument is omitted it defaults to FALSE. If seed ≤ 0 or omitted then no seed is used, while if it is a positive value, then this value is used as a seed.
Other changes
- In the StdRegCoeff(Rx, Ry, Rc, ystd) function, which outputs the standardized regression coefficients, the third argument, Rc, a column array containing the unstandardized regression coefficients, can be omitted, in which case Rc will be calculated from the input data in Rx and Ry using ordinary least squares regression.
- Until now the Anova – Mixed (one fixed and one random factor) and Anova – Random (two random factors) options of the Two Factor ANOVA data analysis tool generated the same output. This is no longer the case. When the Anova – Mixed option is chosen, it is assumed that the Row factor is fixed and the Column factor is random.
- You can now avoid using decimal values for the Outlier Limit and Outlier Multiplier Factor fields on the dialog box for the Descriptive Statistics and Normality data analysis tool. You need to make sure that the Percentage option on the Configuration dialog box (accessed by pressing the Config button on the main menu) is checked and then enter 100 times the value you want for these fields (e.g. enter 250 in the Outlier Multiplier Factor field to set the value to 2.5). Note that if you leave these fields blank, the defaults of 2.2 for the Outlier Multiplier Factor and 2.5 for the Outlier Limit will be used.
Bug Fixes
- Eliminated an extra row filled with #N/A cells in the Adjusted Means output from the ANCOVA data analysis tool when the Standard input format and Include headings options are chosen.
- Blank entries were considered to be zero in the calculation of the whiskers and outliers in the Box Plot with Outliers option of the Descriptive Statistics and Normality data analysis tool. This has now been corrected. Thanks to Chris for identifying this error.
- Corrected the Cutoff value in Logistic Regression data analysis tool when using the Percentage configuration option.
- The Log Base value is now taken into account when calculating the Shannon Index from the Diversity Indices data analysis tool. Thanks to Sun Kim for identifying this error.
- The error in the Wald statistics when using the phi correction in the Poisson Regression data analysis tool has been corrected. Thanks to Sangeeta for identifying this error.