Using Constant Arrays and Array Expressions in Excel Formulas

How Excel handles array expressions and constants in single-cell and multi-cell formulas is not well documented.
So here is my attempt to shed some light on the subject.

What are Constant Arrays?

Constant arrays are Excel formula expressions that allow you to create arrays containing numbers, text, logical values or error values. Constant arrays are enclosed in braces ({}).
{1,”Q”,#N/A,TRUE}
Columns are separated from one another by , and rows are separated by ; so
{1,2,3;”A”,”B”,45.6;#N/A,”Z”,99} creates

You have to enter all the items in a row first before adding ; to move to the next row.
Constant arrays must be rectangular: each row must contain the same number of columns.

Constant arrays do not allow you to create arrays containing Dates or Ranges or Defined Names or Expressions or other Constant Arrays.

What are Array Expressions?

Array expressions are parts of Excel formulas that produce a result array of values when they are evaluated. Some examples are:

A3:A6 – returns a column array of the 4 values in cells A3 to A6
A3:A6*10 – returns a column array of the 4 values in cells A3 to A6, each multiplied by 10
A3:A6*{1;2;3;4} – returns a column array of the 4 values A3*1 , A4*2, A5*3, A6*4
Each element gets multiplied by the corresponding element in the other array

A3:A6+B1:B4 – returns a 4 value column array – A3+B1, A4+B2, A5+B3, A6+B4

What happens when the Arrays in an expression are  different sizes or shapes?

When combining 2 or more component array expressions that are different sizes or shapes Excel expands each of the component arrays so that they are all the same size.

• The expanded arrays will have the same number of rows as the greatest number of rows across all the components.
• The expanded arrays will have the same number of columns as the greatest number of columns across all the components.

Suppose you have 2 ranges A1:B4 and C1:D1

    

Then the array expression A1:B4*C1:D1 will expand each of the component arrays to 4 rows by 2 columns:

and 

Then, since the array expression says multiply, each corresponding element in the first array gets multiplied by the corresponding element in the second array, like this:

which creates this 4 row 2 column result array 

The Expansion Rules

Expanding two component arrays is straightforward if one component is only a single value or a single cell: the single value/cell gets duplicated as required.
This method is also used when one component array is a single row and the other component is a single column.

But Excel does something different when one or more of the components is a 2-dimensional array and the other component array has a miss-matching but greater than 1 number of rows or columns.
Rather than repeat it fills the missing cells with #N/A.

So if you have a range A1:C4 with 3 columns and 4 rows multiplied by a range E1:F1 with 1 row and 2 columns (A1:C4*E1:F1), the E1:F1 gets expanded across by adding a #N/A:

and you get this expansion

which gives this result 

Note that Excel expanded E1:F1 downwards because it was a single row.

Multi-cell Array Formulas

To return the resulting array from an array expression to Excel you need to enter the formula as a multi-cell array formula:

• Select the cells required to match the dimensions of the result array, for instance J1:L4
• Type the formula in the formula bar:
• Enter the formula by holding down Control and Shift and then press Enter.
• The formula bar now shows Excel has added the curly braces to the formula to show you that its an array formula: don’t add the curly braces yourself.

If selected cells smaller than the result array

If the range selected as the output for the multi-cell array formula has less rows or columns than the result array then Excel still calculates the complete result array but only returns the subset of the array values that match the selected cells positions.

If selected cells larger than the result array

If the range selected as the output for the multi-cell array formula has more rows or columns than the result array then Excel pads out the excess cells with #N/A.

Built-in Excel functions designed to return Arrays

Some of the native Excel functions are designed to return multi-cell arrays, including:

• TRANSPOSE
• MODE.MULT
• TREND
• FREQUENCY
• LINEST
• MMULT
• MUNIT
• MINVERSE

Single-cell Array Formulas

Many of Excel’s built-in functions such as SUM, AGGREGATE, MAX, MIN, AVERAGE etc. can calculate array expressions  when entered into a single cell using Ctrl+Shift+Enter.

For example =SUM(A1:A4) can be entered as an ordinary formula, but SUM(A1:A4*10) has to be entered using Control+Shift+Enter, otherwise it just returns #Value.

A few of Excel’s built-in functions such as SUMPRODUCT can calculate array expressions without being entered as an array formula.