Array Formulas in Excel

In Excel, array formulas can be used for linear albegra, matrix math and manipulation, data analysis, conditional arithmatic and much more. This page is a compilation of array formula tips and examples for savvy Excel users. For an introduction to array formulas in Excel, I recommend reading Chip Pearson's article.

Entering Array Formulas

• Tip #1: When using an Array Formula, you press Ctrl+Shift+Enter instead of just Enter after entering or editing the formula.
• Tip #2: An Array Formula will show curly brackets or braces around the formula in the Formula Bar like this: {=…}
• Tip #3: Array constants (arrays "hard-coded" into formulas) are enclosed in braces ( { } ) and use commas to separate columns and semi-colons to separate rows like this 2(rows)x3(columns) array: {1, 1, 1; 2, 2, 2}

Sequential Number Arrays

For some formulas and operations, you may need to use a vector of sequential numbers like {1; 2; 3; ... n}. You can create a sequential vector from 1 to n using this simple array formula:

ROW(OFFSET($A$1,0,0,n,1))
-or-
ROW(1:n)

Important: Although it doesn't matter what is contained in cell A1, if you delete the cell (by removing row 1 or column A for example), insert a row above or a column to the left of cell A1, or cut and paste cell A1 to a different location, your array formula will be messed up. To avoid this problem, use the INDIRECT function to reference cell A1 instead:

ROW(OFFSET(INDIRECT("A1"),0,0,n,1))
-or-
ROW(INDIRECT("1:"&n))

Variant #1: Create a Sequence of Whole Numbers from i to j

If you want to hard-code the values for i and j into the formula, the array formula ROW(i:j) is all you need. If you want i and j to be cell references, use

ROW(INDIRECT(i&":"&j))

Variant #2: Create an n x 1 Vector of Whole Numbers Starting From s

To create the array {s; s+1; s+2; ... s+n-1} use

s+ROW(OFFSET(INDIRECT("A1"),0,0,n,1))-1
-or-
ROW(INDIRECT(s&":"&s+n-1))

Variant #3: Create an n x 1 Vector of Sequential Powers of 10

To create the array {1; 10; 100; 1000; ... 10^(n-1)} use

10^(ROW(OFFSET(INDIRECT("A1"),0,0,n,1))-1)
-or-
10^(ROW(INDIRECT("1:"&n))-1)

Creating the ONES Vector and ONES Matrix

A common vector used in linear algebra is the ONES vector: j = {1;1;1...}. This n x 1 array of 1's can be created using the following array formula.

(1+0*ROW(OFFSET(INDIRECT("A1"),0,0,n,1)))
-or-
(1+0*ROW(INDIRECT("1:"&n)))

The ONES matrix J, an n x n matrix of 1's, can be created from the ONES vector by multiplying j by the transpose of j. Substitute the above formula in place of j in the formula below:

MMULT(j,TRANSPOSE(j))
-or-
MMULT(1+0*ROW(INDIRECT("1:"&n)),TRANSPOSE(1+0*ROW(INDIRECT("1:"&n))))

A more general formula for creating an n x k matrix of ONES is

IF(ISERROR(OFFSET(INDIRECT("A1"),0,0,n,k)),1,1)

ROW or COLUMN Summation using the ONES Vector

It turns out that the ONES vector is very important in statistics for performing a very simple matrix operation - summing the rows or columns. Let's say you have a range of size n (rows) x k (columns). The SUM(range) function in Excel returns the sum of ALL values in the range. You could either use the SUM function separately for each row or column, or you could use the following matrix formulas:

Column-Sum: To the sum the values within each COLUMN of the matrix and return the sums as a 1 x n array (or row vector), use

TRANSPOSE(MMULT(TRANSPOSE(range),(1+0*ROW(INDIRECT("1:"&ROWS(range))))))

Row-Sum: To the sum the values within each ROW of the matrix and return the sums as a k x 1 array (or column vector), use

MMULT(range,(1+0*ROW(INDIRECT("1:"&COLUMNS(range)))))

Creating an IDENTITY Matrix

To create the identity matrix using a formula, rather than by entering it manually, you can use the following array function where value=1:

IF(ROW(OFFSET(INDIRECT("A1"),0,0,n,n))=COLUMN(OFFSET(INDIRECT("A1"),0,0,n,n)),value,0)

If you want to put the values of an n x 1 vector along the diagonal, then replace value with the reference to the range containing the vector.

Element-Wise Multiplication of 2 Matrices

You can perform element-wise multiplication of 2 matrices by simply multiplying two ranges and entering the function as an Array Formula. For example, the formula ={1,2;3,4}*{a,b;c,d} would return the array {1*a,2*b;3*c,4*d}. If one matrix has more columns or rows than the other, those values will be truncated from the result.

Creating a DIAGONAL Matrix

Element-wise multiplication of matrices can be used to create a Diagonal matrix. A Diagonal matrix is a special matrix in which all of the off-diagonal terms are zeros. To create the Diagonal matrix, you multiply the matrix by the identity matrix of the same size:

range*identity
-or-
range*( IF(ROW(OFFSET(INDIRECT("A1"),0,0,ROWS(range),ROWS(range))) = 
COLUMN(OFFSET(INDIRECT("A1"),0,0,COLUMNS(range),COLUMNS(range))),1,0) )

Many programs (but not Excel) include a function like diag(matrix) which returns an n x 1 vector containing the diagonal terms of an n x n matrix. To represent the Diagonal matrix as a column vector, you can use the row-sum operation diag(M)=MI:

MMULT(range*identity,(1+0*ROW(INDIRECT("1:"&COLUMNS(range)))))

Findin the TRACE of a Square Matrix

The trace of a square matrix is just the sum of the diagonal elements. Therefore, the formula for calculating the trace is just:

SUM(range*identity)
-or-
SUM(diagonal)

Repeating Rows or Columns

Sometimes, you may need to form a matrix by repeating a row or column of numeric values. This can be done using matrix multiplication and the ONES vector I defined above, where j is an n x 1 vector.

If you want to create a matrix with n rows by repeating r={1, 2, 3, ...}, use the array formula: MMULT(j,r).

If you want to create a matrix with n columns by repeating r={1;2;3;...}, use the array formula: MMULT(r,TRANSPOSE(j)).

Example: The following formula creates an n x k matrix with n rows where each row is the sequence 1, 2, 3, ... k.

MMULT(1+0*ROW(INDIRECT("1:"&n)),TRANSPOSE(ROW(INDIRECT("1:"&r))))

Alternative to COUNTIF

Let's say you want to count the number of times a value appears within a range. You could use the COUNTIF function which is NOT entered as an array formula. But, the array function shown below is actually slightly faster, making it useful if the range is very large:

COUNTIF(range,"="&lookup_value)
-or-
SUM(1*(range=lookup_value))

The trick to this is realizing that range=lookup_value returns an array of boolean values (TRUE or FALSE) and multipling the boolean array by 1 converts the boolean values into 0's for FALSE and 1's for TRUE. The SUM function is then used to count all the 1's.

Array Formulas in IF Statements

Chip Pearson provides some great examples of ways to use array formulas in IF statements within the SUM and AVERAGE functions to errors and ignore zero values.

LOOKUP the Category for Unique Value in a Table

I was recently asked to find a way to look for a unique value in a table and return the corresponding column label containing that value. I could be wrong, but VLOOKUP and INDEX/MATCH can't be used for this purpose without a bunch of nested IF statements (to first figure out which column the value is in).

This CAN be done using a mega array formula.

First, let's analyze the following formula which returns the Column Index within the range named array that contains the unique lookup_value.

SUMPRODUCT( (lookup_value=array)*1, 
MMULT(1+0*ROW(INDIRECT("1:"&ROWS(array))), 
TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS(array))))))

Now, to get the label for the column that the lookup_value is in, we can use the INDEX function as shown below, replacing col_index with the above formula. The IF statement is needed because if the item is not found in the array, the col_index will be zero.

IF(col_index=0,NA(),INDEX(label_range,col_index))

Matrix Diagnostics

In linear algebra, it is helpful to know certain characteristics of a matrix. Luckily, Excel provides a function for calculating the determinant of a matrix, MDETERM(), which can then be used to help identify other characteristics.

• The rank of a matrix is the number of linearly independent rows. The number of linearly independent rows is equal to the number of linearly independent columns. If a matrix is n x p, the matrix is said to be full rank if the rank is equal to the smaller of n and p.
• If a square matrix is full rank, then it is called a nonsingular matrix, and it has a unique inverse. If a square matrix is less than full rank, then a unique inverse does not exist and it is called a singular matrix. You can use the MINVERSE() function in Excel to find the inverse. A nonsingular matrix has a nonzero determinant.
• A symmetric matrix is positive definite if x'Ax>0 for all possible vectors x except x = 0. In simpler terms, a positive definite matrix can be factored into a sort of "square root" (not really, but it's kind of analogous to a square root) like A=B'B where B is an n x p matrix of rank p < n. All the diagonal elements of a positive definite matrix are positive.
• The determinant of a singular matrix is zero. The determinant of a nonsingular matrix is nonzero. The determinant of a positive definite matrix is positive. You can use the MDETERM() function in Excel to find the determinant. The MDETERM() function returns a constant and does not require you to enter the formula as an array formula.
• Two vectors a and b of the same size are orthogonal if a'b=0, or MMULT(TRANSPOSE(a),b)=0
• A vector a is said to be normalized if a'a=1, or MMULT(TRANSPOSE(a),a)=1. A normalized vector is also called a unit vector because the magnitude or length of a normalized vector is equal to 1.
• A square matrix is orthogonal if each of the columns is a normalized vector and the columns are mutually orthogonal. Orthogonal matrices are used in robotics, kinematics, and statistics to perform rotations about an axis.
• The sum of the eigenvalues of a square matrix is equal to the trace of the matrix. This is a good double-check after you have calculated the eigenvalues.

Normalize a Vector

To normalize a vector, you divide the vector by its magnitude using an array formula. The magnitude of a vector a is denoted |a| and is equal to the square root of the sum of the squares.

SQRT(SUM(range^2))
-or-
SQRT(SUMSQ(range))   :Not an Array Formula

range/SQRT(SUM(range^2))
-or-
range/SQRT(SUMSQ(range))

Linear Regression

The trend lines in an Excel chart allow you to do simple linear regression, but you can also do linear regression in Excel using matrix and array functions. It's much easier to just use the LINEST function, but for fun I give the general formula for calculating the b matrix (the least squares estimators) when you have the y and X matrix. Or in other words, if you want to solve for b starting from y=Xb, you can do that using the formula b=(X'X)^-1X'y which in Excel is:

MMULT(MMULT(MINVERSE(MMULT(TRANSPOSE(x),x)),TRANSPOSE(x)),y)

Alternate XNPV Function

If for some reason you don't like Excel's XNPV function or for some reason you need to use 360 days in a year instead of 365, you can use the following array formula in place of XNPV, where r is the discount rate.

SUM(values_range/((1+r)^((date_range-INDEX(date_range,1))/365)))