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