**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

**MPOWER**(*A, n*) =* A ^{n}*

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)

is there an Excel array function to determine the eigenvalues of a square, real symmetric matrix?

You can use the Real Statistics eVALUES function for this.

Charles

Hi Charles,

For your example of normalizing the Vector A [6, -1, 2, 5] the corresponding values [0.738, -.12309, 0.246, 0.614] are correct but they do not add up to 1. They add up to approximately 1.477.

Is this a typo or is it something bigger that I’m not understanding?

Thank you.

Actually, nevermind, I understand now. If you determine the length of that vector by taking the square root of the summation of their squares then that equals 1.

Got it.

Can I have fundamental concept of matrices. I mean why do I need Matrices in my life and how I am going to use matrices in my every day life.

As I can use Trigonometry for building a accurate Building or A Robot. ( As I can use trigonometry to calculate accurately angles and Ratios of Physical objects)

Most people probably don’t need to use matrices in their every day life, but if you want to perfrom certain statistical analyses matrices is very helpful.

Charles

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

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.