Mix-up about the covariance in Excel

One of the Excel mix-ups in its statistical functions that lasted longer is about the computation of the covariance.

Until recently (we think that this change was only introduced in the 2007 version) Excel provided two functions for the variance and standard deviation, one,for population and the other for sample data.

However, there was only one function for the covariance that returned the value for population data. There was (an there is) also only one function for the correlation, which is right, since the correlation values are the same for population ans sample data. This situation could lead to the erroneous conclusion that the covariance also has the same value for both cases.

The fixing of this situation was made by introducing two formulas for the computation of the covariance.

However, the procedure Covariance  of the Analysis ToolPak still returns the population covariances. Additionally, it also maintains another heritage mix-up. Although the Toolpak general methodology presents fixed values in the output, that does not change automatically if data values change, in this case the diagonals of the covariance matrix contain the formula to compute the population variance. This means that changes in the data will automatically change values in the diagonal, contrary to what happens for the other elements of the matrix.

The matrix.xla add-in, presented in another message also has only one function for the covariance matrix that returns the values for population data.

Generating correlated normal random numbers - Part 01

This function generates five columns containing numbers (the headers are not generated by the function):

1. Order number (i)
2.  Uniform random sequence (Z1)
3. Uniform random sequence (Z2)
4. First combination of Z1 and Z2  >>> Z3
5. Second combination of Z2 and Z1 >>> Z4

The pairs (Z1,Z3) and (Z2,Z4) simulate a sample extracted from populations in which the pairs have a chosen value for the correlation. As it happens in sampling you usually get a value for the sampling correlation that differs from the population.

The values returned can be unstandardized specifying for each case the mean and standard deviation.

If you are not satisfied with the outcome, recalculate the spreadsheet. As the random values are generated by VBA code they are not volatile as it happens when you input the Excel built-in RAND function in a spreadsheet cell.

See more about volatility in the follow link in a post entitled Handle Volatile Functions like they are dynamite:

http://chandoo.org/wp/2014/03/03/handle-volatile-functions-like-they-are-dynamite/

  

The inputs are:

1. Number of lines in the sequences (k)
2. Value of population correlation  

Source:
https://www.blogger.com/blogger.g?blogID=3230308801600609435#editor/target=post;postID=4988601992196925693;onPublishedMenu=posts;onClosedMenu=posts;postNum=1;src=postname

In a future message the remaining models in the source will be presented.

B6:E9	{=xlCorrMtx(B12:E41)}
A12:E41	{=xlRndNormal(C3,E3)}
H6:K9	{=xlCorrMtx(H12:K41)}
H12	=H$3+B12*H$4

Function xlRndNormal(k As Integer, corr)     With WorksheetFunction           Dim i As Integer         Dim rslt()         ReDim rslt(1 To k, 1 To 5)           For i = 1 To k             rslt(i, 1) = i         Next i           For i = 1 To k             rslt(i, 2) = .Norm_S_Inv(Rnd)         Next i           For i = 1 To k             rslt(i, 3) = .Norm_S_Inv(Rnd)         Next i           For i = 1 To k             rslt(i, 4) = corr * rslt(i, 2) + Sqr(1 - corr ^ 2) * rslt(i, 3)         Next i         For i = 1 To k             rslt(i, 5) = corr * rslt(i, 3) + Sqr(1 - corr ^ 2) * rslt(i, 2)         Next i         xlRndNormal = rslt           End With End Function

The code for the function xlCorrMtx is in this blog at

https://www.blogger.com/blogger.g?blogID=3230308801600609435#editor/target=post;postID=4988601992196925693;onPublishedMenu=posts;onClosedMenu=posts;postNum=1;src=postname

Sample covariance matrix

Function xlCovSMtx(rng As Range)         Dim i, j     Dim nCls As Integer     Dim mtx()         With WorksheetFunction             nCls = rng.Columns.Count         'MsgBox nCls         ReDim mtx(1 To nCls, 1 To nCls)             For i = 1 To nCls             For j = 1 To nCls                 mtx(i, j) = .Covariance_S(rng.Columns(i), rng.Columns(j))             Next j         Next i             xlCovSMtx = mtx         End With End Function Function xlCovSMtx_U(rng As Range)         Dim i, j     Dim nCls As Integer     Dim mtx()         With WorksheetFunction             nCls = rng.Columns.Count         'MsgBox nCls         ReDim mtx(1 To nCls, 1 To nCls)             For i = 1 To nCls             For j = 1 To nCls                 If i <= j Then                     mtx(i, j) = .Covariance_S(rng.Columns(i), rng.Columns(j))                 Else                     mtx(i, j) = 0                 End If             Next j         Next i             xlCovSMtx_U = mtx         End With End Function Function xlCovSMtx_L(rng As Range)         Dim i, j     Dim nCls As Integer     Dim mtx()         With WorksheetFunction             nCls = rng.Columns.Count         'MsgBox nCls         ReDim mtx(1 To nCls, 1 To nCls)             For i = 1 To nCls             For j = 1 To nCls                 If i >= j Then                     mtx(i, j) = .Covariance_S(rng.Columns(i), rng.Columns(j))                 Else                     mtx(i, j) = 0                 End If             Next j         Next i             xlCovSMtx_L = mtx         End With End Function

Correlation matrix

Function xlCorrMtx(rng As Range)         Dim i, j     Dim nCls As Integer     Dim mtx()         With WorksheetFunction             nCls = rng.Columns.Count         'MsgBox nCls         ReDim mtx(1 To nCls, 1 To nCls)             For i = 1 To nCls             For j = 1 To nCls                 mtx(i, j) = .Correl(rng.Columns(i), rng.Columns(j))             Next j         Next i             xlCorrMtx = mtx         End With End Function Function xlCorrMtx_U(rng As Range)         Dim i, j     Dim nCls As Integer     Dim mtx()         With WorksheetFunction             nCls = rng.Columns.Count         'MsgBox nCls         ReDim mtx(1 To nCls, 1 To nCls)             For i = 1 To nCls             For j = 1 To nCls                 If i <= j Then                     mtx(i, j) = .Correl(rng.Columns(i), rng.Columns(j))                 Else                     mtx(i, j) = 0                 End If             Next j         Next i             xlCorrMtx_U = mtx         End With End Function Function xlCorrMtx_L(rng As Range)         Dim i, j     Dim nCls As Integer     Dim mtx()         With WorksheetFunction             nCls = rng.Columns.Count         'MsgBox nCls         ReDim mtx(1 To nCls, 1 To nCls)             For i = 1 To nCls             For j = 1 To nCls                 If i >= j Then                     mtx(i, j) = .Correl(rng.Columns(i), rng.Columns(j))                 Else                     mtx(i, j) = 0                 End If             Next j         Next i             xlCorrMtx_L = mtx         End With End Function

---

 [AT1]

 [AT2]

The matrix.xla add-in

This add-in for Excel 2000/XP is composed by 4 files:

• matrix.xla
• matrix.hlp
• matrix.csv (*)
• FunCustomize.dll  (**)

(*) "matrix.csv" can be used only if you have XNUMBERS 2.4 package. In that case put the CSV file in the same directory of xnumbers. The Xnumbers function handbook will be able to load also the new functions of "matrix.xla"

(**)  appears by courtesy of Laurent Longre  (http://longre.free.fr)

It is available for download at The downloads section at (the sites of matrix.xla and FunCustomize are no longer available).

http://www.bowdoin.edu/~rdelevie/excellaneous/

that contains also other applications from the same team (Leonardo Volpi; John Beyers).

Unfortunately, if it is not my fault, some problems arise when working with this add-in in recent versions of Excel. However, since the code is open you can access the VBA code and import the desired routines to your workbook or even to an add-in.

In some cases, the routines import process is not straightforward since there are routines that call other routines, implying the necessity of tracing the computation flow. Another option that seem to work is the importation of the relevant VBA modules.

Another limitation is that, as it seems, Microsoft ceased to support the old format help files. To sidestep this situation you can use the add-in tutorials that are available in several sites, namely

Volume 1

https://msu.edu/course/fw/877/bence/matrix_1.8/MatrixTutorial.pdf

Volume 2

www.cs.bsu.edu/homepages/kerryj/kjones/MatrixTutorial2.pdf

 You can also covert the add-in (xla) to a Workbook format performing the steps indicated in 

http://www.mrexcel.com/forum/excel-questions/2189-convert-xla-xls.html

• Press Alt F11
• Press Ctrl R
• In the window pane click the "Thisworkbook" object
• Press F4
• Scroll down this window until you see
• IsAddin
• Change the property to False
• Now save the workbook as xls

The workbook obtained has a sheet with detailed information about the routines listed below. As far as I understand, these are only the master routines that call auxiliary routines not listed.  

One simple way to get all procedures listed is 

• Copy the entire codes to an Excel spreadsheet column
• Sort the contents of the column
• Delete the rows that do not contain procedure declarations

Name	Function Description
Gauss_Jordan_step	Gauss Jordan algorithm step by step
Gram_Schmidt	Gram-Schmidt's Orthonormalization
Interpolate	Interpolation with polynomials
M_ABS	Euclidean Norm of vector or matrix
M_ADD	Addition of matrices
M_BAB	Similarity transform [B]*[A]*[B]^-1
M_DET	Determinant
M_DET_C	Determinant for complex matrix
M_DET3	Determinant for tridiagonal matrices
M_DIAG	Diagonal matrix from a vector
M_DIAG_ERR	Diagonalization error
M_EXP	Matrix series expansion e^[M]
M_EXP_ERR	Truncation error of matrix expansion series
M_ID	Matrix Identity (I)
M_INV	Matrix inverse [A]^-1
M_INV_C	Complex Matrix inverse [A]^-1
M_MULT_C	Complex matrices multiplication
M_MULT3	Mutliplication for tridiagonal matrix
M_POW	Power of matrix [A]^n
M_PROD	Product of matrices [A]*[B]*[C]*….
M_PROD_S	Matrix multiplication for a scalar
M_RANK	Rank of matrix
M_SUB	Subtraction of matrices
M_T	Matrix transpose
M_TRAC	Trace
M_TRIA_ERR	Triangolarization error
Mat_Adm	Returns the Admittance matrix of a linear passive network
Mat_BlokPerm	Returns the permutation vector of block-partitioned matrix
Mat_Blok	Returns the block-partitioned matrix
Mat_Cholesky	Cholesky decomposition
Mat_Hessemberg	Hessemberg form
Mat_Hilbert	Returns Hilbert's matrix
Mat_Householder	Returns Houseolder matrix
Mat_Leontief	Returns the Leontief inverse matrix of Input Output Analysis
Mat_LU	LU decomposition
Mat_QR	QR decomposition
Mat_QR_iter	Performs the diagonalization with the QR iterative method
Mat_Tartaglia	Returns Tartaglia's matrix
Mat_Vandermonde	Returns Vandermonde's matrix
MatCharPoly	Characteristic polynomial coefficients
MatCmpn	Companion matrix
MatCorr	Correlation matrix
MatCovar	Covariance matrix
MatDiagExtr	Diagonal extractor
MatEigenvalue_Jacobi	Eigenvalues of symmetric matrix with Jacobi algorithm
MatEigenvalue_max	Dominant eigenvectors with powers' method
MatEigenvalue_pow	Eigenvectors with powers' method
MatEigenvalue_QL	Eigenvalues of tridiagonal matrix
MatEigenvalue_QR	Eigenvalues with QR algorithm
MatEigenvalue_TridUni	Eigenvalues of tridiagonal uniform matrix
MatEigenvector	Eigenvector of eigenvalue
MatEigenvector_C	Complex eigenvector of eigenvalue
MatEigenvector_inv	Eigenvector of  eigenvalue
MatEigenvector_Jacobi	Eigenvectors of symmetric matrix with Jacobi algorithm
MatEigenvector_max	Dominant eigenvalues with powers' method
MatEigenvector_pow	Eigenvalues with powers' method
MatEigenvector3	Eigenvectors of tridiagonal matrix
MatExtract	Extract sub-matrix
MatMopUp	Matrix mop-up of round-off errors
MatNorm	Vector or Matrix Norm
MatNormalize	Vectors Normalization
MatOrtNorm	Orthonormalization
MatPerm	Permutation matrix
MatRnd	Random matrix
MatRndEig	Random matrix with given eigenvalues
MatRndEigSym	Random symmetric matrix with given eigenvalues
MatRndRank	Random matrix with given rank or determinant
MatRndSim	Random symmetric matrix with given rank or det.
MatRot	Returns the orthogonal planar rotation matrix
MatRotation_Jacobi	Jacobi's rotation matrix
Path_Floyd	All-pairs-path of Graph with Floyd's algorithm
Path_Min	Returns the shortest path of a Graph with Floyd's algorithm
Poly_Roots	Polynomial rootfinder with Lin-Bairstow method
Poly_Roots_QR	Polynomial rootfinder with QR method
ProdScal	Scalar Product (inner)
ProdScal_C	Complex scalar product
ProdVect	Vector Product 3D
REGRL	Linear regression with SVD
REGRP	Polynomial regression
RRMS	root mean squares
Simplex	Linear Optimization with Simplex method
SVD_D	Singular Value Decomposition [U]*[D]*[V]^t: returns D
SVD_U	Singular Value Decomposition [U]*[D]*[V]^t: returns U
SVD_V	Singular Value Decomposition [U]*[D]*[V]^t: returns V
SYSLIN	Solve Linear System [A]x=b
SYSLIN_C	Solve a Complex Linear System [A]x=b
SYSLIN_ITER_G	Solve Linear System with Gauss-Seidel algorithm
SYSLIN_ITER_J	Solve Linear System with Jacobi algorithm
SYSLIN_T	Solve triangular linear sistem
SYSLIN3	Solve tridiagonal linear system
SYSLINSING	Solve Singular Linear System [A]x=b
TRASFLIN	Linear Transform
VarimaxIndex	Returns the Varimax index of a given Factors matrix
VarimaxRot	Computes the orthogonal rotation with Varimax Kaiser's
M_MULT_TPZ	Multiplies a Toeplitz matrix for a vector
SYSLIN_TPZ	Solve Toeplitz Linear System [A]x=b
M_TPZ_ERR	Toeplitz matrix error

Utilities: Documenting spreadsheet formulas

All the elements presented are not warranted to be correct or free from defects. Please report any errors found to  afstblogs@gmail.com

Function xlGetFormula( _     cell As Range, _     Optional tp As Integer = 1, _     Optional shw As Boolean = True _ ) As Variant   Dim _   st(1 To 2) As String, _   sst As String     st(1) = cell.Address(0, 0)   If cell.HasArray = False Then     st(2) = cell.Formula   Else     st(2) = "{" & cell.Formula & "}"   End If     If shw = False Then         xlGetFormula = ""     ElseIf tp = 1 Then         sst = st(2)         xlGetFormula = sst     ElseIf tp = 2 Then         sst = st(1) & ": " & st(2)         xlGetFormula = sst     ElseIf tp = 3 Then         xlGetFormula = st     ElseIf tp <> 1 And tp <> 2 And tp <> 3 Then         xlGetFormula = "ERR_type"     End If   End Function

NOTES: 

1. The FORMULATEXT is a spreadsheet function that cannot be used in VBA and supports returns the name of the function in the idiom active in the spreadsheet.
2. The xlGetFormula function always returns the function name in English 

Matrixes: Trace of a square matrix

All the elements presented are not warranted to be correct or free from defects.  Please report any errors found to  afstblogs@gmail.com

The trace of a square matrix is the sum of the diagonal elements of the matrix.

Function xlMtxTrace(Mtx)          ' Mtx - Matrix for which the trace will be computed     ' If the matrix is not square the funtion returns the message     '    m<>n          m = Mtx.Rows.Count     n = Mtx.Columns.Count          If m <> n Then         xlMtxTrace = "m<>n"     Else         For i = 1 To n             xlMtxTrace = xlMtxTrace + Mtx(i, i)         Next i     End If      End Function