Most data in an Excel workbook can be thought of as a matrix. Thirty-two Simetar© functions that facilitate the manipulation and analysis of data in matrices are accessed by clicking the Data Manipulation and matrix operation functions icon (Figure 2). The matrix functions are presented in alphabetical order:

Data Manipulation and matrix operation functions

|
Figure 2. Matrix Operation Menu |
|
|
|
Center Matrix of a Specified Dimension
Cofactor of a Square Matrix
Column Vector to a Diagonal Matrix
Column Vector to a Matrix
Column Vector to a Toeplitz Matrix
Determinant of a Square Matrix
Eigenvalues of a Square Matrix
Eigenvectors of a Square, Symmetric Matrix
Equicorrelation Matrix of a Specified Dimension
Exponential Power of a Matrix
Factor a Square, Symmetric Matrix
Generalized Inverse of a Matrix
Inner Product of Two Matrices
Invert a Nonsingular Square Matrix
Kronecker Multiply Two Matrices
Mahalanabis Distance of Two Data Matrices
|
|
Matrix of 1s
Matrix to a Vector
Multiply Two Matrices
Norm of a Matrix
Orthoganalize a Matrix
Rank of a Matrix
Reduced Row Echelon Form of a Matrix
Reverse a Column or Row of Values
Row Echelon Form of a Matrix
Sequence of Numbers
Sort a Matrix by a Specified Column
Sweep a Square Matrix on a Diagonal Element
Trace of a Square Matrix
Transpose a Matrix
Wishart Matrix of Random Variables |
|
|
|

|
Figure 3. Dialog Box for Changing a Vector to a Matrix. |
The most frequently used matrix functions are described in detail in this section. The Simetar© Matrix and array functions are dynamic so changes made to the data are automatically observed in the output functions. For example, changes to the input data will change the associated correlation matrix, the Cholesky decomposition matrix of the correlation matrix, and subsequent calculations. Most all of the matrix functions are demonstrated in the Excel workbook DemoSimetar-Mat.
Column Vector to a Matrix.
The Matrix Operations dialog box accessed by selecting the Data manipulation and matrix operation functions icon contains a function to Change a Column Vector to a Matrix (Figure 3). An example of this function is provided in Step 8 of DemoSimetar-Mat. The task of rearranging the data from vector to matrix format also can be accomplished using the Simetar© function =BLOCKIT( ). The parameters for the function, demonstrated in Figure 4 for the equation editor:
=BLOCKIT (Data Series, No. of Rows, No. of Columns, Fill Order)

Data manipulation and matrix operation functions
where: Data series is the location of the vector, No. of rows is the number of rows for the matrix, No. of columns is the number of columns for the matrix, Fill order is ‘1’ to fill by row and ‘0’ to fill by column. The =BLOCKIT( ) function is an array function so you must press Control Shift Enter after typing the right hand parenthesis. An example of converting a vector to a matrix is provided in Step 8 of DemoSimetar-Mat. This function is one which benefits from using Excel’s equation editor, as demonstrated in Figure 4. If you are not familiar with Excel’s equation editor, read Section 17.

|
Figure 4. Example of Using Excel’s Equation Editor to Program the BLOCKIT Function. |
Convert a Matrix to a Vector.
The task of converting a matrix of weekly, monthly, or quarterly data to a vector for time series analysis is simplified with the Matrix to a Vector function. To use this function indicate the matrix to operate on and the output location for the vector. See Step 7 in the DemoSimetar-Mat workbook. This option uses the Simetar© function =STRETCHIT( ).

|
Figure 5. Factor a Square Matrix Dialog Box. |
Factor a Square Matrix (Choleski Decomposition).
Simetar© can be used to factor the covariance or correlation matrix for advanced simulation applications using theData manipulation and Matrix operation functions icon. This function can also be used by directly applying the =MSQRT( ) function. To use the dialog box, first select the Factor a Square, Symmetric Matrix option and then specify the Output Range and the location of the matrix to factor (do not include labels) via the Select Matrix Range box (Figure 5). Copy the labels from the source matrix to the factored matrix as Simetar© does not use the row and column labels as input for this option. See Step 2 in DemoSimetar-Mat for an example.

Data manipulation and Matrix operation functions
The square root (or factor) of a square matrix can also be calculated using the Simetar© function. The array function is demonstrated in Step 3 of DemoSimetar-Mat. The =MSQRT( ) array function is used as follows:
highlight a square matrix on the worksheet that is the same size as the matrix to be factored, type =MSQRT (Location of Square Matrix), an example might be = MSQRT (C24:G29), and press the Control Shift Enter keys. This three key step is required because =MSQRT( ) is an array function.
Invert a Square Matrix (Excel).
The Invert a Nonsingular Square Matrix option in the Matrix Operations dialog box is demonstrated in Figure 6. (Simetar© uses Excel’s function at this point but provides a menu so you do not have to memorize the function name.) Select this option and then specify the input matrix (highlight only the numbers) and the output range for the upper left hand value, then click OK. The inverse of the input matrix will appear in the worksheet without row/column names. Copy and paste in the names if needed. An example of inverting a square matrix is provided in Step 1 of DemoSimetar-Mat.

|
Figure 6. Matrix Operations Dialog Box. |
Generalized Inverse of a Rectangular Matrix.
The Generalized Inverse of a Matrix function in the Matrix Operations dialog box uses Simetar’s function. Select this option and then specify the input matrix (highlight only the numbers) and the output range for the upper left hand value, then click OK. The inverse of the input matrix will appear in the worksheet without row/column names. Copy and paste in the names if needed. An example of inverting a square matrix is provided in Step 22 of DemoSimetar-Mat.
Sweep a Square Matrix on a Diagonal Element.
The Sweep a Square Matrix on a Diagonal Element option in the Matrix Operations dialog box performs this matrix operation. Select this option and then specify the input matrix (highlight only the numbers), the reference diagonal element row number (an integer), and the output range for the upper left hand value, then click OK. See Step 23 in workbook DemoSimetar-Mat for an example.
Transpose a Matrix (Excel).
A matrix can be transposed by selecting the Transpose a Matrix option in the Matrix Operations dialog box, specifying the matrix to transpose and the upper-left hand cell to anchor the output matrix. This procedure simplifies Excel’s TRANSPOSE function by eliminating the need to block the area for the transposed matrix. See Step 6 of DemoSimetar-Mat for an example.
Multiply Two Matrices (Excel).
Excel’s matrix multiplication, MMULT, function is made easier by selecting the Multiply Two Matrices option in the Matrix Operations dialog box. The function is demonstrated in Step 10 of DemoSimetar-Mat.
Kronecker Multiply Two Matrices.
The Kronecker product of two matrices is accomplished by selecting this option in the Matrix Operations dialog box. The function is demonstrated in Step 12 in the workbook DemoSimetar-Mat.
Convert a Vector to a Diagonal Matrix.
In simulation it is useful to convert a vector of standard deviations to a diagonal matrix. The Simetar© function =MDIAG( ) can be used to convert an array to a diagonal matrix using the Column Vector to a Matrix option in the Matrix Operations dialog box. A demonstration of how to convert an array to a diagonal matrix is provided in Step 20 of the DemoSimetar-Mat.
Find the Determinant of a Square Matrix.
The determinant of a square matrix can be calculated by selecting the Determinant of a Square Matrix option in the Matrix Operations dialog box. The Excel function =MDETERM (square matrix) is used for this calculation. An example is provided in Step 5 of DemoSimetar-Mat.
Find the Eigen Values of a Square Matrix.
The Eigen values for a square matrix can be calculated by selecting the Eigenvalues of a Square Matrix option in the Matrix Operations dialog box. An example is provided in Step 24 of DemoSimetar-Mat.
Find a Cofactor of a Square Matrix.
The cofactor of a square matrix can be calculated by selecting the Cofactor of a Square Matrix option in the Matrix Operations dialog box. An example is provided in Step 25 of DemoSimetar-Mat.
Find the Trace of a Square Matrix.
The trace of a square matrix (or the sum of the diagonal elements) can be calculated using the Trace of a Square Matrix option in the Matrix Operations dialog box. The trace can also be calculated using the Simetar© function =MTRACE (square matrix). An example of calculating the trace of a square matrix is provided in Step 4 of DemoSimetar-Mat.
Find the Rank of a Matrix.
The rank of a matrix can be calculated to determine if the matrix is singular. If an NxN matrix has an N rank if it is nonsingular. The rank of a square matrix can be calculated using the =MRANK( ) function or the Matrix Operations dialog box Rank of a Matrix option. An example of this function is provided in Step 16 of the DemoSimetar-Mat workbook.
Find the Norm of a Matrix.
The norm (the square root of the inner product of the matrix) can be calculated using the find the Norm of a Matrix option in the Matrix Operations dialog box. This option uses the =MNORM( ) function and is demonstrated in Step 9 of the DemoSimetar-Mat workbook.
Find the Inner Product of Two Matrices.
The inner product of two matrices can be calculated directly using this option in the Matrix Operations dialog box. If two matrices are of the same dimension, MxN, the inner product scalar is the sum of the products of the corresponding elements. The scalar can be calculated directly using the function =MIP(matrix 1, matrix 2) and is demonstrated in Step 11 of DemoSimetar-Mat.