**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* = [*a _{ij}*] where 1 ≤

*i*≤

*r*and 1 ≤

*j*≤

*c*. Thus

*a*is the element in the

_{ij}*i*th row and

*j*th 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* = [*a _{ij}*] and

*B*= [

*b*] are

_{ij}**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.

*a*=

_{ij}*b*for all

_{ij }*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 *a _{11}*, …,

*a*. The

_{kk}**trace**of

*A*is

*a*+ … +

_{11}*a*. If all the elements in the square matrix

_{kk}*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

*I*to emphasize that it is a

_{k}*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* = [*a _{ij}*] 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 2^{nd} row and 3^{rd} column is *a _{23}* = 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 [*a _{i}*] where 1 ≤

*i*≤

*r*and a row vector as [

*a*] where 1 ≤

_{j}*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 [*a _{i}*] 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 *i*th row and *j*th column of *A*, i.e. *a _{ij}*, 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

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:

=INDEX(A5:D8,F5:F8,F5:F8)

Thank you for this tutorial! It is great. I was a bit confused at first by the:

INDEX(A5:D8,F5:F8,F5:F8)

But then I realized that F5:F8 meant that the formula was to be repeated for each row in G in turn.

sir, i have question for you. How to create matrix like identity matrix from random matrix?

Vicky,

I don’t quite understand your question. By a random matrix do you mean a matrix whose elements are random numbers? By create do you mean in Excel?

Charles