Definition 1: An r × c matrix (also called an array) is a rectangular array (or table) with r rows and c columns. We can represent such a matrix as A = [aij] where 1 ≤ i ≤ r and 1 ≤ j ≤ c. Thus aij is the element in the ith row and jth column.
In Excel a matrix is represented as a rectangular range. E.g. B3:C7 can be considered to be a 5 × 2 matrix.
Two matrices A = [aij] and B = [bij] are equal, denoted A = B, if they have the same size and shape (i.e. the same number of rows and columns) and all the corresponding elements are equal (i.e. aij = bij for all i, j with 1 ≤ i ≤ r and 1 ≤ j ≤ c).
Definition 2: A square matrix is one where r = c. The main diagonal of a square k × k matrix A is a11, …, akk. The trace of A is a11 + … + akk. If all the elements in the square matrix A are 0 then the matrix is called the null matrix. If all the elements on the main diagonal are 1 and all other elements are 0 then the matrix is called an identity matrix, denoted I (or Ik to emphasize that it is a k × k identity matrix).
A triangular matrix has either all zeros in the triangular portion below the main diagonal (an upper triangular matrix) or all zeros in the triangular portion above the main diagonal (a lower triangular matrix). A diagonal matrix is one that is both an upper triangular matrix and a lower triangular matrix, i.e. all the entries off the main diagonal are zero.
Example 1: Define matrix A = [aij] as follows:
A is a 3 × 3 square matrix whose main diagonal is 3, 5, 2. The trace of is 3 + 5 + 2 = 10. The element in the 2nd row and 3rd column is a23 = 4. A is an upper triangular matrix.
Definition 3: A vector A is an r × c matrix in which either r = 1 or c = 1. If c = 1 then A is called a column vector. If r = 1 then A is called a row vector. We can represent a column vector as [ai] where 1 ≤ i ≤ r and a row vector as [aj] where 1 ≤ j ≤ c.
When r = c = 1 then A is called a scalar. In this case A is the same as an ordinary number (thus [a] is considered to be the same as a).
If A is a row vector [ai] the length of A, denoted ‖A‖ is
(and similarly for a column vector). A unit vector has length 1. To normalize a vector is to divide the vector by its length so as to yield a unit vector.
Excel Functions: Excel provides the following functions, where we use the same symbol A for both the matrix and the range in the worksheet which contains the matrix.
ROWS(A) = the number of rows in matrix A
COLUMNS(A) = the number of columns in matrix A
Also note that the element in the ith row and jth column of A, i.e. aij, is given by the formula INDEX(A, i, j).
Real Statistics Excel Functions: The following supplemental Excel functions are included in the Real Statistics Resource Pack:
ISCELL(A) = TRUE if A is a scalar and FALSE otherwise
ISSQUARE(A) = TRUE if A is a square matrix and FALSE otherwise
LENGTH(A) = the length of matrix ; i.e. =SQRT(SUMSQ(A))
NORM(A) = array function which outputs a normalized version of array A; i.e. = A/LENGTH(A)
DIAGONAL() = array function which outputs a square matrix whose main diagonal consists of the elements in A and which has zeros everywhere else
DIAG(A) = array function which outputs a column vector with the diagonal of A
TRACE(A) = trace of A
IDENTITY() = outputs an identity matrix of the highlighted square shape
MPOWER(A, n) = An
You can also explicitly identify the size of the identity by using the formula IDENTITY(k). In this case the output is a k × k identity matrix.
Example 2: Find the length of the vector A in range B4:B7 of Figure 1 and normalize this vector.
Figure 1 – Normalizing a vector
The result is shown in Figure 1. The length of A is shown in cell B9, which contains the formula =SQRT(SUMSQ(B4:B7) or the supplemental formula =LENGTH(B4:B7). The value for this example is 8.124.
To normalize vector A simply divide each element in A by its length. E.g. cell D4 contains the formula =B4/B$9. The normalized vector, A′ is shown in the range D4:D7. The length of this vector is 1, as can be seen in cell D9.
Example 3: Create a column vector with the elements on the diagonal of the matrix in the range A5:D8 of Figure 2:
Figure 2 – Extracting the elements from the diagonal of a square matrix
Using the approach from Table Lookup, we can create a set of indices 1 to 4 (since the matrix is 4 × 4) and then the vector of diagonal elements contained in range G5:G8 is calculated by the array formula: