I am pleased to announce Release 2.17 of the Real Statistics Resource Pack. The new release is available for free download (Download Resource Pack) and is compatible with Excel 2002, 2003, 2007, 2010 and 2013. The versions for Excel 2010 and 2013 are available now; those for Excel 2002, 2003 and 2007 will be available later today.
The Real Statistics Examples Workbook has been updated to reflect the new release. You can also download this file for free (Download Examples). The website is being updated to reflect the new features. These changes will be available shortly.
The release provides the following new functions:
New distribution and test functions
UNIFORM_DIST(x, α, β, cum) = the pdf of the uniform distribution function f(x) at x with parameters α and β when cum = FALSE and the corresponding cumulative distribution function F(x) when cum = TRUE.
FIT_TEST(R1, R2, par) = CHISQ.DIST.RT(χ2, df) where R1 = the array of observed data, R2 = the array of expected values, par = the number of unknown parameters (default = 0) and χ2 is calculated from R1 and R2 with df = the number of elements in R1 (or R2) – par – 1
Inverse functions for standard distributions
NEGBINOM_INV(p, k, pp) = smallest integer x such that NEGBINOM.DIST(x, k, pp, TRUE) ≥ p
HYPGEOM_INV(p, n, k, m) = smallest integer x such that HYPGEOM.DIST(x, n, k, m, TRUE) ≥ p
POISSON_INV(p, μ) = smallest integer x such that POISSON.DIST(x, μ, TRUE) ≥ p
UNIFORM_INV(p, α, β) = x such that UNIFORM_DIST(x, α, β, TRUE) = p
WEIBULL_INV(p, α, β) = x such that WEIBULL.DIST(x, α, β, TRUE) = p
New logistic regression function
LogitCoeffs(R1, iter): array function which outputs an n+3 × 1 range, where n = # of columns in R1, consisting of the n+1 logit coefficients for R1, an empty cell and the # iterations actually made; iter = maximum # of iterations (default 20).
This is a simpler version of the current LogitCoeff function with the following added feature. If the algorithm fails before the number of iterations in iter, then the coefficients obtained to that point as well as the # of successful iterations are reported.
New power functions
BINOM_POWER(p0, p1, n, tails, α) = the power of a one sample binomial test when p0 = probability of success on a single trial based on the null hypothesis, p1 = expected probability of success on a single trial, n = the sample size, tails = # of tails: 1 or 2 (default) and α = alpha (default .05).
CORREL1_POWER(r0, r1, n, tails, α) = the power of a one sample correlation test using the Fisher transformation when r0 = the population correlation (based on the null-hypothesis), r1 = the effect size (observed correlation), n = the sample size, tails = # of tails: 1 or 2 (default) and α = alpha (default .05).
Required sample size functions
Associated with the new power functions are the following new functions that calculate the required sample size for certain tests:
BINOM_SIZE(p0, p1, 1-β, tails, α) = the sample size of a one sample binomial test required to achieve power of 1-β (default .8) when p0 = probability of success on a single trial based on the null hypothesis, p1 = expected probability of success on a single trial, tails = # of tails: 1 or 2 (default) and α = alpha (default .05).
CORREL1_SIZE(r0, r1, 1-β,tails, α) = the sample size required to detect an effect of size of r1 (observed correlation) with power 1-β (default .80) when the population correlation (based on the null-hypothesis) is r0, tails = # of tails: 1 or 2 (default) and α = alpha (default .05).
Correlation coefficient test functions
New functions are provided for the one sample t-test of the correlation coefficient (valid when the population correlation coefficient is zero). Similar functions using the Fisher transformation already exist.
CorrTTest(r, size, tails) = the p-value of the one sample test of the correlation coefficient using a t-test where r is the observed correlation coefficient based on a sample of the stated size. If tails = 2 (default) a two-tailed test is employed, while if tails = 1 a one tailed test is employed.
CorrTLower(r, size, alpha) = the lower bound of the 1 – alpha confidence interval of the population correlation coefficient based on a sample correlation coefficient r coming from a sample of the stated size.
CorrTUpper(r, size, alpha) = the upper bound of the 1 – alpha confidence interval of the population correlation coefficient based on a sample correlation coefficient r coming from a sample of the stated size.
CorrelTTest(r, size, alpha, lab): array function which outputs t-stat, p-value, and lower and upper bound of the 1 – alpha confidence interval, where r and size are as described above. If lab = TRUE then output takes the form of a 2 × 4 range with the first column consisting of labels, while if lab = FALSE (default) then output takes the form of a 1 × 4 range without labels.
CorrelTTest(R1, R2, alpha, lab) = CorrelTTest(r, size, alpha, lab) where r = CORREL(R1, R2) and size = the common sample size, i.e. the number of pairs from R1 and R2 which both contain numeric data.
In addition, the following previously supported function has been enhanced with the addition of a tails parameter.
CorrTest(rho, r, size, tails) = the p-value of the one sample test of the correlation coefficient using the Fisher transformation where rho is the expected population correlation coefficient and r is the observed correlation coefficient based on a sample of the stated size. If tails = 2 (default) a two-tailed test is employed, while if tails = 1 a one tailed test is employed.
Bug fixes
The MAD function has been revised so that it works properly even when some data is missing.
The p-value output from the CorrelTest and Correl2Test functions are now for the two-tail test instead of the one-tail test.