I am pleased to announce Release 5.5 of the Real Statistics Resource Pack. The new release is now available for free download at Download Resource Pack for Excel 2007, 2010, 2011, 2013 and 2016 (Windows and Mac version) environments.
The various examples workbooks have also been updated for compatibility with the new release. The Real Statistics website will be updated over the course of the next several days to reflect the new capabilities.
The following is a summary of the new features in Release 5.5.
Confidence intervals for effect size
The following array functions have been added that calculate Cohen’s d effect size, Hedge’s g unbiased estimate of the effect size and the 1-α confidence interval of d
T_EFFECT1 and TT_EEFECT1 – for a one sample t test
T_EFFECT2 and TT_EFFECT2 – for a two independent samples t test
T_EFFECT2P and TT_EFFECT2P – for a paired samples t test, using dav, i.e. the version of Cohen’s d whose variance is the average of the variances of the two samples
The confidence intervals are based on a confidence interval for the noncentrality parameter. For the one and two sample t tests, there is also an option to use the Hedges and Olkin estimate of the confidence interval.
Confidence intervals for statistical power
The following array functions have been added that return the power of a t test along with the 1-α confidence interval of the power.
T1_POWER_Conf – for a one sample t test or paired samples t test
T2_POWER_Conf – for a two independent samples t test
Trimmed means and Yuen-Welch t tests
The following new functions have been added that implement a version of the two independent sample t test when some assumption is not met.
TRIMTEST – used to address departures from normality, especially those due to the presence of outliers. Essentially this is a t test using the trimmed means of each sample as the means and the Winsorized variances as the variances.
YUENTEST – used when both the normality and equal variances assumptions are violated. Essentially this is a combination of the Welch’s t test and the trimmed means t test.
Fligner-Policello Test
The following two new functions have been added that carry out the Fligner-Policello test:
FPSTAT – returns the statistic for the Fligner-Policello test
FPTEST – returns the p-value for the Fligner-Policello test
This nonparametric test determines whether population medians corresponding to two independent samples are equal. The test assumes that the populations are symmetric about their medians, although the test doesn’t require that the distributions have the same shape nor that their variances be equal.
Jonckheere–Terpstre Test
The Jonckheere–Terpstre test is similar to the Kruskal-Wallis test, but is used when there is a perceived order to the groups. The following new array function has been added to support this test:
JT_TEST – returns the JT statistic, z-score and p-value for the Jonckheere–Terpstre test
New charting capabilities
The following two new data analysis tools have been added (to the Desc tab):
Multiple Scatter Charts – This data analysis tool creates one (x, y) scatter chart for the sample of each independent variable x versus the samples for the dependent variable y. This makes it easier to test the linearity assumption for multiple regression analysis.
Change Axes Min/Max – This data analysis tool makes it easier to change the minimum and maximum values of the horizontal and vertical axes in a chart. This is especially useful when a chart is generated whose range of axis values is too wide for the data.
Confidence Ellipse
A new Confidence Ellipse data analysis tool has been added (to the Multivar tab). This data analysis tool displays a chart of the 1-α confidence ellipse and shows which sample points are within the confidence ellipse and which are outside.
The following new functions have been added as well:
CONF_MNORM – returns a column array with the axis lengths of the 1-α confidence hyper-ellipse axes based on a multivariate normal distribution
CONF_BNORM – returns a column array with the axis lengths of the 1-α confidence ellipse axes based on a bivariate normal distribution plus theta (angle) and percentage of data elements inside the ellipse
CONF_VOLUME – returns the volume (area) of the 1-α confidence ellipse
MOUTLIERS – tests an array each of whose rows represents a k-tuple of values for multivariate outliers using the Mahalanobis distance squared and a chi-square test
Breusch-Godfrey Test
The following new functions have been added to support the detection of p-order autocorrelation.
BGSTAT – returns the statistic for the Breusch-Godfrey test
BGTEST – returns the p-value for the Breusch-Godfrey test
Newey-West Standard Errors
Just as Huber-White’s robust standard errors can be used to correct the standard errors of regression coefficients when heteroskedasticity is present, the Newey-West HAC standard errors can be used to correct the standard errors of regression coefficients when autocorrelation, and possibly heteroskedasticity, are present.
The following new array function has been added:
HACCoeff – returns the OLS regression coefficients (column 1) along with the HAC (heteroskedasticity and autocorrelation corrected) standard errors (column 2)
Confidence interval for AUC
The following new functions have been added to create a confidence interval for the Area under the Curve (AUC)
AUC_LOWER(auc, n1, n2, α) – lower limit of the 1-α confidence interval for AUC
AUC_UPPER(auc, n1, n2, α) – lower limit of the 1-α confidence interval for AUC
Box Plot with Outliers Enhancement
When using the Box Plot with Outliers data analysis tool, a list of outliers is now displayed. Also, whereas previously there was a limit as to how many outliers could be shown in the box plot, namely 248, there is no longer any such limit.
Complex number enhancements
Added the following array functions:
CPolar(z) – converts the complex number z (represented as a 1 × 2 array (a, b) in rectangular form, i.e. a + bi, to polar form, represented as a 1 × 2 array of form (r, θ)
CRect(u) – converts a complex number in polar form to rectangular form
CRoots(z, n) – returns an array with the nth roots of the complex (or real) number z
The CSub(z1, z2) and CMult(z1, z2) functions have been enhanced so that z1 and z2 can either be complex numbers (1 × 2 arrays) or real numbers. The CDiv(z1, z2) function has been enhanced so that z1 can be either a complex or real number. CAdd function has been enhanced to allow the addition of up to 5 complex or real numbers.
Cubic Root Formula
The following array function has been added that implements the cubic roots formula. This formula is similar to the quadratic formula (although more complicated) and finds the three roots (two of which may be complex) of a cubic polynomial.
CubicRoots(R1) = a 3 ⨯ 2 array, in which each row contains one unique root of the polynomial x3 + bx2 + cx + d (where R1 is a 3 ⨯ 1 column array containing the values b, c and d); each root is a 1 ⨯ 2 array where the first element represents the real part of the root and the second element represents the imaginary part.
Two-sided versions of trimming and Winsorizing
The array functions WINSORIZE(R1, p) and TRIMDATA(R1, p) have been enhanced to allow the option of having a third argument p1. When p1 is present, the bottom p% of the data is trimmed or Winsorized and the top p1% of the data is trimmed or Winsorized. When p1 is not present, then the bottom and top p/2% of the data is trimmed or Winsorized.
An optional third argument is now also available for WINMEAN(R1, p, p1). When this option is used the mean of the Winsorized data is returned. Since the equivalent function for trimmed data is a standard Excel function, a new Real Statistics function TRIM_MEAN has been added, where
TRIM_MEAN(R1, p, p1) = AVERAGE(TRIMDATA(R1, p, p1))
Other Enhancements
- The Sig (significance) value (yes or no) in the output from Dunn’s test after the Kruskal-Wallis test is replaced by the p-value
- The MPROD array function has been added that allows the matrix multiplication of 3 or 4 arrays (equivalent to nested calls to MMULT).
Bug Fixes
- Changed the # Items label to # of Raters in the dialog boxes for the ICC(1,1) statistical power and sample size when using the Statistical Power and Sample Size data analysis tool.
- Fixed an error in the TRegCoeff0 function
- Replaced the term “2 × 1 array” by “1 × 2 array” in the various complex number function descriptions
Thanks so much, Pech!