Real Statistics Release 7.10

I am pleased to announce Release 7.10 of the Real Statistics Resource Pack. The new release is now available for free download at Download Resource Pack for Excel 2010, 2013, 2016, 2019, 2021 and 365 in both Windows and Mac environments.

Over the course of the next couple of days, the website will be updated for compatibility with the new release.

A new math examples workbook is now available that contains all the examples on such mathematical topics as derivatives, root finding, prime numbers and linear algebra. The other examples workbooks are in the process of being updated with the inclusion of examples from the last few releases and with the removal of examples now included in the new math examples workbook.

I want to thank everyone who has made suggestions or has identified errors in the website or software. I appreciate your help in improving the utility and accuracy of Real Statistics.

I also want to express my appreciation to all of you who have donated to Real Statistics. These donations help to offset the costs of maintaining the website. If you are getting value from the Real Statistics website or software, I would appreciate your donation by going to Please Donate.

The following is an overview of the new features in Release 7.10:

Generalized Extreme Value Distribution

The following new worksheet functions have been added for the GEV distribution.

GEV_DIST(x, μ, σ, ξ, cum) = the pdf of the GEV distribution when cum = FALSE and the corresponding cumulative distribution function when cum = TRUE.

GEV_INV(p, μ, σ, ξ) = the inverse of the GEV distribution at p

Here, μ is the location parameter, σ is the scale parameter and ξ is the shape parameter.

Data in a column array R1 can be fit to a GEV distribution using the method of moments, as implemented via the following worksheet function:

GEV_FITM(R1, lab, xguess, iter, prec, incr, pure): returns a column array with the estimated values for μ, σ and ξ as well as the LL based on these parameters

If lab = TRUE (default FALSE) then an extra column of labels is appended to the output. xguess is the initial guess for ξ (default -.1). iter (default 100) = the maximum number of iterations. The algorithm will terminate prior to iter iterations when the error is less than prec (default .0000001). incr is used to calculate the derivative of the log-likelihood function and defaults to .000001. If pure = FALSE (default), then the sample values of standard deviation and skewness are used; otherwise, the population values of the standard deviation and skewness are used.

Data in a column array R1 can be fit to a GEV distribution using the maximum log-likelihood estimates, as implemented via the following worksheet function:

GEV_FIT(R1, lab, iter, prec, incr, mguess, sguess, xguess): returns a 3 × 4 array; the first column of the output contains the estimated values for μ, σ, ξ; the second column contains error values (values near zero indicate that the iteration converged successfully); the third column contains the standard errors of the parameter estimates; the fourth column contains the LL value based on the estimated parameter values.

If lab = TRUE (default FALSE) then an extra row and an extra column of labels are appended to the output. mguess, sguess, xguess are the initial guesses for μ, σ, ξ. iter, prec and incr are as for GEV_FITM.

The following worksheet function has also been added.

GEVSKEW(ξ) = the skewness for a GEV distribution with the specified ξ value.

In addition, the MEAN_DIST and VAR_DIST worksheet functions have been amended to support the mean and variance of a GEV distribution.

Fréchet Distribution

The following new worksheet functions have been added for the Fréchet distribution.

FRECHET_DIST(x, α, β, γ, cum) = the pdf of the Fréchet distribution when cum = FALSE and the corresponding cumulative distribution function when cum = TRUE.

FRECHET_INV(p, α, β, γ) = the inverse of the Fréchet distribution at p

Here, γ is the location parameter,  β is the shape parameter and α is the scale parameter.

In addition, the MEAN_DIST and VAR_DIST worksheet functions have been amended to support the mean and variance of a Fréchet distribution.

Harrell-Davis Quantiles

The following worksheet function has been added:

HD_QUANTILE(R1, p) = the pth Harrell-Davis quantile for the data in the column array R1.

The Harrell-David median is calculated by the formula =HD_QUANTILE(R1,.5).

Double MAD

The following worksheet function has been added:

DoubleMAD(R1, lower, harrell) = the double median of absolute values of the data in the column array R1 from the median of the values in R1.

If harrell = FALSE (default), then the ordinary median is used; otherwise, the Harrell-Davis median is used. If lower = TRUE (default), then the lower version of the Double MAD is returned; otherwise, the upper version is returned.

Confidence Interval for Coefficient of Variation

The following array function has been added:

CV_CONF(R1, lab, ctype, short, tails, alpha): returns a column array with the values: CV of the data in R1, an unbiased version of the CV and the 1-alpha confidence interval for the CV. Different estimates are available depending on the choice of ctype.

Root-finding Functions

Using the approach for the DERIV and INTEGRAL functions provided in the previous release, the following functions have been added that output a root of any function f(x), i.e. a value x = c such that f(c) = 0.

Here, R1 is a cell that contains a formula that represents the function f(x) and R2 is the address of a cell referenced in R1 that points to the variable x. If R2 is omitted, then it defaults to the first cell referenced in R1.

BISECTION(R1, lower, upper, iter, prec, R2) = a root of f(x) in the interval [lower, upper] based on the bisection method.

SECANT(R1, x1, x2, iter, prec, R2) = a root of f(x) based on the secant method and two initial points x = x1 and x = x2.

BRENT(R1, lower, upper, iter, prec, R2) = a root of f(x) in the interval [lower, upper] based on Brent’s method.

NEWTON(R1, guess, iter, prec, incr, R2) = a root of f(x) based on Newton’s method using the specified initial guess.

iter (default 100) = the maximum number of iterations. The algorithm will terminate prior to iter iterations when the error is less than some value based on prec (default .0000001). For BISECTION and BRENT the signs of f(lower) and f(upper) should be different. incr is used to calculate the derivative of f(x) and defaults to .000001.

Since there can be more than one root of a function in an interval, we can also use the following array function to attempt to find all the roots.

NROOTS(R1, lower, upper, nguess, iter, prec, incr, R2): returns a column array with all the roots of f(x) in the interval (lower, upper) based on Newton’s method.

This function finds the roots based on nguess (default 50) initial guesses chosen randomly from the interval (lower, upper). There is no guarantee that all the roots will be found, but for nguess chosen large enough, usually, you should be able to obtain all the roots (up to a maximum of 100 roots).

If nguess < 0 then the process is the same except that -nguess equally spaced guesses are made from lower to upper.

Finding a global or local maximum/minimum

The following worksheet functions have been added to find the global and local maximum or minimum value of a function f(x). As before, R1 is a cell that contains a formula that represents the function f(x) and R2 is the address of a cell referenced in R1 that points to the variable x. If R2 is omitted, then it defaults to the first cell referenced in R1.

MNEWTON(R1, guess, iter, prec, incr, R2): returns a 3 × 1 column array with a critical value c (based on guess), f(c) and the second derivative of f(x) at c, based on Newton’s method.

MNEWTON is used to return a critical value (i.e. a local minimum, maximum or inflection point) for the function f(x). The second derivative is used to determine the type of critical value.

FMIN(R1, lower, upper, nguess, iter, prec, incr, R2) = the global minimum of f(x) in the interval [lower, upper].

FMAX(R1, lower, upper, nguess, iter, prec, incr, R2) = the global maximum of f(x) in the interval [lower, upper].

The FMAX function calculates the values of f(x₀), f(x₁), …, f(x_n) where n = nguess, x₀ = lower, x_n = upper, x_i+1 = x_i + Δx and Δx = (x_n–x₀)/n, and finds the largest f(x_k) of these values. This x_k is then used as an initial guess for MNEWTON to find a possible local maximum. If this value is larger than x_k, then this is likely to be the global maximum. If not then x_k is likely to be the global maximum (or at least near to it). FMIN is calculated in a similar way.

The default for nguess is 1000. The other arguments for FMIN and FMAX are similar to those of the previous functions.

Multivariate functions

The following worksheet function finds the roots of two functions with two variables x and y. Thus, this worksheet function finds a solution to two functions in two variables.

NEWTON2(R1, R2, guessx, guessy, iter, prec, incr, Rx, Ry): a 1 × 4 row array containing a root x,y of f₁(x,y) and f₂(x,y) along with the values of these functions at the root, based on Newton’s method using the initial guesses guessx for x and guessy for y. R1 contains the formula for f₁(x,y) and R2 contains the formula for f₂(x,y).

Similarly, the following worksheet function identifies a potential local maxima and minima of a function in two variables x and y.

MNEWTON2(R1, guessx, guessy, iter, prec, incr, Rx, Ry): a 2 × 5 array containing the values x, y that identify a possible local maxima or minima of f(x,y), based on Newton’s method using the initial guesses guessx for x and guessy for y.

The first column of the output contains the values x, y of a critical point. The second column contains the values of the partial derivatives of f(x,y) at these values (which should be close to zero if there was convergence). The third column contains the value f(x,y) and the type of critical value (local maximum, local minimum, saddle point or indeterminant). The rest of the output contains the 2 × 2 Hessian matrix (consisting of partial second derivatives).

The following worksheet functions identify the global minimum and maximum of a function in two variables x and y.

F2MIN(R1, lox, upx, loy, upy, gx, gy, Rx, Ry): a 1 × 3 row array containing the values x, y, f(x,y) that produces the global minimum of f(x,y) in the ellipse whose x-axis is (lox, upx) and y-axis is (loy, upy) with gx equally-spaced intervals on the x-axis and gy equally-spaced intervals on the y-axis. gx and gy default to 200.

F2MAX(R1, lox, upx, loy, upy, gx, gy, Rx, Ry): a 1 × 3 row array containing the values x, y, f(x,y) that produces the global maximum of f(x,y) in the ellipse whose x-axis is (lox, upx) and y-axis is (loy, upy) with gx equally-spaced intervals on the x-axis and gy equally-spaced intervals on the y-axis. gx and gy default to 200.

Here, R1 and R2 are cells that contain a formula that represents the function f(x,y) and Rx is the address of a cell referenced in R1 (and R2) that points to the variable x. If Rx is omitted, then it defaults to the first cell referenced in R1. Similarly, Ry is the address of a cell referenced in R1 (and R2) that points to the variable y. If Ry is omitted, then it defaults to the second cell referenced in R1.

The following worksheet functions perform similar roles for three variable functions.

NEWTON3(R1, R2, R3, guessx, guessy, guessz, iter, prec, incr, Rx, Ry, Rz): a 1 × 6 row array containing a root x,y,z of f₁(x,y,z),  f₂(x,y,z) and  f₃(x,y,z) along with the values of these functions at the root, based on Newton’s method using the initial guesses guessx for x, guessy for y and guessz for z. R1 contains the formula for f₁(x,y,z), R2 contains the formula for f₂(x,y,z) and R3 contains the formula for f₃(x,y,z).

MNEWTON3(R1, guessx, guessy, guessz, iter, prec, incr, Rx, Ry, Rz): a 3 × 6 array containing the values x, y, z that identify a possible local maxima or minima of f(x,y,z) based on Newton’s method using the initial guesses guessx for x and guessy for y and guessz for z. The output is similar to that from MNEWTON2, except that the third column also contains the determinant of the 3 × 3 Hessian matrix,

F3MIN(R1, lox, upx, loy, upy, loz, upz, gx, gy, gz, Rx, Ry, Rz): a 1 × 4 row array containing the values x, y, f(x,y,z) that produces the global minimum of f(x,y,z) in the hyper-ellipse whose x-axis is (lox, upx), y-axis is (loy, upy) and z-axis is (loz, upz) with gx, gy and gz equally-spaced intervals on the x-axis, y-axis and z-axis. gx , gy and gz default to 50.

F3MAX(R1, lox, upx, loy, upy, loz, upz, gx, gy, gz, Rx, Ry, Rz): a 1 × 4 row array containing the values x, y, f(x,y,z) that produces the global maximum of f(x,y,z) in the hyper-ellipse whose x-axis is (lox, upx), y-axis is (loy, upy) and z-axis is (loz, upz) with gx, gy and gz equally-spaced intervals on the x-axis, y-axis and z-axis. gx , gy and gz default to 50.

Evaluating a function

The following worksheet functions have been added. These functions evaluate a function in one, two or three variables, where the function f is expressed in cell R1 using addresses in Rx, Ry, Rz if necessary as described for F2MIN.

FUNC(x, R1, Rx) = f(x)

FUNC2(x, y, R1, Rx, Ry) = f(x,y)

FUNC3(x, y, z, R1, Rx, Ry, Rz) = f(x,y,z)

Surface chart assistance

Excel provides a limited surface charting capability. We will use this capability to create 3-dimensional plots of functions in two variables z = f(x,y). The following worksheet function has been added to create the data needed for such charts.

FUNC2D(R1, xlo, xup, ylo, yup, xincr, yincr, prec, Rx, Ry): returns an array with data for a function f(x,y) expressed in cell R1 for x values in the range xlo to xup in increments of xincr and for y values in the range ylo to yup in increments of yincr. 

R1, Rx and Ry are as for the F2MIN function described previously. prec is a small positive number (default 0.00000001). Any row or column heading value that is less than or equal to prec will be treated as zero.

Second derivative

The following worksheet function has been added.

DERIV2(x, R1, incr, R2) = the second derivative f ′′(x) where R1 is a cell that contains a formula that represents the function f(x) and x is the value contained in cell R2. R2 is the address of a cell referenced in R1 that points to the variable x. If R2 is omitted, then it defaults to the first cell referenced in R1. incr is the increment value; default .000001.

In addition, the existing DERIV and INTEGRAL functions have been modified slightly to avoid errors when negative constants are used.

Gradient and Hessian

The following worksheet functions have been added. These array functions calculate the gradient vector and Hessian matrix for a function in two or three variables, where the function is expressed in cell R1 using addresses in Rx, Ry, Rz if necessary as described for F2MIN.

GRADIENT2(x, y, R1, incr, Rx, Ry): 2 × 1 array containing the gradient of f(x,y)

GRADIENT3(x, y, z, R1, incr, Rx, Ry, Rz): 3 × 1 array containing the gradient of f(x,y,z)

HESSIAN2(x, y, R1, incr, Rx, Ry): 2 × 2 array containing the Hessian of f(x,y)

HESSIAN3(x, y, z, R1, incr, Rx, Ry, Rz): 3 × 3 array containing the Hessian of f(x,y,z)

Pseudo Inverse

The existing PseudoInv(R1, iter, prec) function has been modified as follows:

• The default for prec has been changed to .00001.
• Support for a Gaussian elimination approach to finding the pseudo-inverse has been added. This option is used when iter is set to a negative value.

Proportional Test

A small modification has been made to the One-sample Proportion Test data analysis tool so that the output from the binomial version of the test agrees with the output from R.