I am pleased to announce Release 9.9 of the Real Statistics Resource Pack. The new release is now available for free download in Download Resource Pack for Excel 2010, 2013, 2016, 2019, 2021, 2024, and 365 for both Windows and Mac environments. It contains a lot of new capabilities.
I want to thank everyone who made suggestions or identified errors on the website or in the software. Your help has improved 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 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 9.9.
Markov Chains
This release adds the new Markov Chain Network Diagram data analysis tool to the Corr tab.
Click here for more details.
Worksheet Functions
This release also adds the following new worksheet functions supporting Markov chains. Here, Rp contains the Markov chain transition matrix, and Rd contains the transpose of the distribution vector π.
MarkovProb (Rp, Rd, n): returns a k × 1 distribution array for xn, i.e. πPn
MarkovSim(Rp, Rd, n): returns a column array with n entries from the simulation of a Markov chain based on the k × k transition matrix in Rp and the k × 1 initial distribution array Rd. As for MarkovProb, the second argument can take an integer value between 1 and k.
MarkovSimProb(Rp, Rd, n, m): returns a k × 1 distribution array with an estimated xn based on the average of the distribution values from m simulations.
Click here for more details.
For the following two functions, start is the initial state, and Ra is a column array containing the elements in A.
MarkovSimHit (Rp, start, Ra, n, m): returns a 2 × 1 array. The first entry is the fraction of the m simulations that hit A (i.e. for which xj belongs to A for some j ≤ n). The second entry is the average of min {h: xh ∈ A} restricted to the simulations that hit A.
MarkovSimHitFreq (Rp, start, Ra, n, m): returns an array with two columns. The first two entries in column 2 are the same as those output by MarkovSimHit. The remaining entries in column 2 estimate the probability of hitting A in 1 to n steps based on the m simulations (only entries with non-zero probability are listed). Column 1 contains labels for the corresponding entries in column 2.
MarkovSimReturn (Rp, start, n, m): returns a k × 3 array based on m simulations, each of length n, starting from state start. For each state s = 1, …, k, column 1 provides the probability that the Markov chain returns to s. Column 2 provides the estimated first return (when there is a return), and column 3 provides the average frequencies of returns (when there is a return) divided by n.
Click here for more details.
Parameter Estimation Function
The following worksheet function has also been added.
MarkovParams(R1, k): returns a k × k+1 array, where the leftmost k × k array is the estimated transition matrix and the rightmost k × 1 column array is the estimated distribution, based on the observed state data in R1. k defaults to the largest state in R1.
Click here for more details.
Random Walks
RandWalkDist(i, n, p) = P(xn = i) for the random walk Markov chain with the stated value of p (the probability of one step to the right)
RandWalkDist(i, n, p, j) = the sum of P(xn = k) for k = i to j, for the random walk Markov chain with the stated value of p.
The following functions are based on m random walk simulations, each of maximum length n, where p = the probability of one step to the right, and start is the starting state (default 0).
RandWalkSim(p, n, start, bound1, bound2): returns an n × 1 array with the states of the random walk or gambler’s ruin simulation.
RandWalkSimReturn(p, n, m, start): returns a 4 × 2 array whose second column contains (1) the probability that the random walk returns to start, (2) the expected number of steps needed to return to start, (3) the average farthest distance from start when there is a return to start, and (4) the average farthest distance from start when there isn’t a return to start. The first column contains labels for the second column.
Click here for more details.
Hidden Markov Models
This release also adds the following functions in support of Hidden Markov Models (HMM). Here, Rp contains the k × k Markov chain transition matrix, Rd contains the transpose of the distribution vector π, and Rq contains the k × m emission matrix.
HMM_Sim(Rp, Rd, Rq, n): returns an n × 2 array whose first column contains a sequence of n hidden states from a simulation run of the HMM, and whose second column contains the corresponding observations.
HMM_SimX(Rq, Rs): returns an n × h array with the observations corresponding to the hidden states in the h runs of the HMM, each of length n, in Rs.
Click here for more details.
HMM_Forward (Rp, Rd, Rq, n, total): returns an n × k array containing the αi(s) values if total = FALSE (default). If total = TRUE, then a column is appended to the output containing the P(z1:i) values.
HMM_Backward (Rp, Rd, Rq, n, total): returns an n × k array containing the βi(s) values if total = FALSE (default). If total = TRUE, then a column is appended to the output containing the P(z1:n) value in the first row and blanks elsewhere.
Click here for more details.
HMM_Params(Rs, Ro, k, m): returns a k × k+m+1 array with an estimate of the transition, distribution, and emission matrices that produced the hidden states in Rs and corresponding observations in Ro. k defaults to the largest state in Rs, and m defaults to the largest observation in Ro.
Click here for more details.
Viterbi Algorithm
Where Ro is an n × 1 array containing observations:
HMM_Viterbi0(Rp, Rd, Rq, Ro): returns an n × k+k array, with the full output of the Viterbi Algorithm
HMM_Viterbi(Rp, Rd, Rq, Ro): returns an n × 1 array with the sequence of hidden states output from the Viterbi algorithm.
Where Ro is an n × h array containing multiple sequences of observations:
HMM_ViterbiX(Rp, Rd, Rq, Ro): returns an n × h array with the hidden states corresponding to the observations in R0, generated by the Viterbi algorithm.
Click here for more details.
HMM_ParamsX(Rp, Rd, Rq, Ro, iter): returns a k × k+m+2 array with the revised transition, distribution, and emission matrices employing the observations in Ro, and the Viterbi algorithm to iteratively produce corresponding states for use by the algorithm in HMM_Params.
Click here for more details.
Baum-Welch Algorithm
Where Ro is an n × 1 array containing observations:
HMM_Rho(Rp, Rd, Rq, Ro): returns a revised k × k transition matrix using one iteration of the Baum-Welch algorithm
HMM_Pi(Rp, Rd, Rq, Ro): returns a revised k × 1 distribution array using one iteration of the Baum-Welch algorithm
HMM_Kappa(Rp, Rd, Rq, Ro): returns a revised k × m emission matrix using one iteration of the Baum-Welch algorithm
HMM_Baum(Rp, Rd, Rq, Ro, iter, prec): returns a k × k+m+2 array with the revised transition, distribution, and emission matrices from the Baum-Welch algorithm
Where Ro is an n × h array containing multiple sequences of observations:
HMM_RhoX(Rp, Rd, Rq, Ro): returns a revised k × k transition matrix using one iteration of the Baum-Welch algorithm
HMM_PiX(Rp, Rd, Rq, Ro): returns a revised k × 1 distribution array using one iteration of the Baum-Welch algorithm
HMM_KappaX(Rp, Rd, Rq, Ro): returns a revised k × m emission matrix using one iteration of the Baum-Welch algorithm
HMM_BaumX(Rp, Rd, Rq, Ro, iter, prec): returns a k × k+m+2 array with the revised transition, distribution, and emission matrices from the Baum-Welch algorithm
Click here for more details.
Difference Equations
For a first-order difference equation of the form xn = axn-1 + b, the following worksheet function has been added:
DiffEqn1(a, b, x0, n, prop) = xn
Click here for more details.
For a second-order difference equation of the form xn+1 + bxn + cxn-1 = a, the following worksheet functions have been added:
DiffEqn2X(a, b, c, x0, x1): returns an 8 × 2 array with the following values in column 2: d, multiplier of d (none = 1, n, or n-sq), root1, root2, A, B, formula for xn, stability (converge or diverge). Column 1 contains the appropriate labels. If the roots are complex, then root1 and root2 are replaced by the amplitude r and angle θ. x0 and x1 default to 0.
DiffEqn2(a, b, c, x0, x1, n, prop) = xn
Click here for more details.
Chebyshev Norm and Distance
The existing Minkowski Lp capabilities have been enhanced by the addition of the Chebyshev distance option, essentially the Minkowski distance where p = ∞. The Chebyshev distance is used when p is set to 0 in the LpNORM worksheet function.
Click here for more details.
Cluster Analysis Enhancements
The existing cluster analysis capabilities have been enhanced by the addition of the Chebyshev distance option. The Chebyshev distance is used when p is set to 0 in any of the following:
K-Means Cluster Analysis data analysis tool
CLUSTERS, CLUST_Converge, CLUST_Anal, CLUST, CLUSTErr, and CENTROIDErr worksheet functions
Click here for more details.
Conversion of Decimal to Fraction
This release adds the Dec2Frac worksheet function that converts a decimal number into a fraction of the form num/den, where num and den are positive integers.
Click here for more details.
Color Enhancements
This release increases the number of named colors from 66 to 128. A few minor changes have also been made to a couple of the original 66 named colors. These changes are reflected in the Color Assignment data analysis tool as well as the GetColor, FillColor, FontColor, RGBCode, and ColorDistSq worksheet functions.
The Color Assignment data analysis tool now adds a button that displays the named color when clicked on.
This release adds the following functions:
RGBX(red, green, blue) returns the RGB coding for a color with the stated red, green, and blue components.
RGBRev(n) returns the Blue + Green * 256 + Red * 2562 RGB coding when n is the RGB coding used by Excel, and returns the Excel RGB coding when n is the Blue + Green * 256 + Red * 2562 RGB coding.
Click here for more details.
The following worksheet functions concern the XYZ and L*a*b* codings. These color codes are supposed to better capture human perception of colors.
RGB2LAB(red, green, blue) returns a 1 × 3 array with the L*a*b* coding for a color with the referenced red, green, and blue components.
RGB2XYZ(red, green, blue) returns a 1 × 3 array with the XYZ coding for a color with the referenced red, green, and blue components.
XYZ2LAB(x, y, z) returns a 1 × 3 array with the L*A*B* coding for a color with the referenced X, Y, and Z components.
ColorCodes(rgb1) returns a 1 × 3 array with the L*, a*, and b* codes corresponding to the referenced RGB code.
Click here for more details.
Distance between colors
In addition to the ttype = 0, 1, and -1 approaches to the squared distance between two colors used by the RGBDistSq(rgb1, rgb2, ttype) and ColorDistSq(color1, color2, ttype) functions, we now add ttype = 2, where the squared distance is
(L1 – L2)2 + (a1 – a2)2 + (b1 – b2)2
based on the L*A*B* coding corresponding to the RGB coding of the two colors.
A ttype argument has also been added to the FillColor(R1, approx, ttype) and FontColor(R1, approx, ttype) worksheet functions. This release also adds the following related function:
GetColor(rgb1, approx, ttype) returns the named color corresponding to the referenced RGB code, or the closest approximation where approx and ttype are as for FillColor and FontColor.
Click here for more details.