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

Arrays1

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

Arrays3     Arrays2

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

Arrays4 and  Arrays5

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:

Arrays7 which creates this 4 row 2 column result array  Arrays6

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:

Arrays8 and Arrays9 you get this expansion

Arrays10 which gives this result  arrays11

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: Arrays12
  • Enter the formula by holding down Control and Shift and then press Enter.
  • The formula bar now shows Arrays13 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.

Advertisement
This entry was posted in arrays, Calculation and tagged , , , , . Bookmark the permalink.

10 Responses to Using Constant Arrays and Array Expressions in Excel Formulas

  1. moonoo says:

    Thank you for the great explanation.

  2. sam says:

    Define an Array Constant : mArray = {1,2,3}
    Define a Range =mRng = Sheet1!$A1:$B$1 (containing 4,5,6)
    Define a Name Combo = Choose({1;2},mArray,mRg)
    Type =Combo in Cell D1 and evaluate this returns a 2 X 3 Array containing {1,2,3;4,5,6}
    Constant Arrays can be combined with other type of Array Constants, Ranges etc

    • fastexcel says:

      Neat example using CHOOSE (one of my favourite functions)! But thats a formula returning an array not an constant array enclosed by {}. The bit between the {} is the bit that cannot contain the other types.

      • sam says:

        @Charles
        You can try this
        Define mConst1 as ={1}
        Define mConst2 = {2}
        If you want to combine the two in to 1 Constant Array
        Even though you cant do {mConst1,mConst2}
        you can define mConst = {1,0}*mConst1+{0,1}*mConst2 this will produce an Array Constant of {1,2}
        You can replace {1} with {“01-Jan-2015”} and {2} and {“14-Jan-2014}
        mConst will produce as array of data type Long ={41640,41653}
        The Choose Version = Choose(1,2},mConst1,mConst2)
        will produce a array of Data Type String = {“01-jan-2014″,”14-Jan-2014”}
        If mConst1 = {1} and mConst2 = {“Hello”}
        The choose version will result in an Array of Mixed Data Types
        ={1,”Hello”}

  3. sam says:

    Sorry mRng = Sheet1!$A$1:$C$1

  4. Colin Legg says:

    Nice post, Charles.

    Just picking up on the formula in the single cell array formula section, here’s a small point which might be of interest to some:

    On my version of Excel (2013), =SUM(A1:A4*10) [not CSE entered] doesn’t return #VALUE! if it is entered in a cell in rows 1:4 in the sheet. For example, if you enter the formula in row 3 then it will effectively return the result of =SUM(A3*10), in other words using the row number to decide which value in the range to multiply by 10. In this particular case it’s really the * operator rather than SUM() that requires the CSE entry for the full range to be dereferenced.

  5. Pingback: Excel Roundup 20150119 « Contextures Blog

  6. akshay thakker says:

    Hi All:

    I am working on a math for a firm (Firm A) that issues loans with different payment terms every month – they basically earn the spread between the borrowing and lending cost. The cost of the funds (Investors) also keeps varying each month. They need to keep track of the total loan outstanding and the interest payable towards their own borrowing cost (cost of fund). I was able to calculate the emi that is due to firm A and the Principal that needs to be paid to the Investor based on that emi. However, I need to calculate the cost of fund that Firm A incurs every month. I figured that I need to be able to calculate the total principal outstanding per loan that Firm A borrowed, so that i can use an array function to calculate the interest cost to firm A towards the Investors.

    Please see the attached excel sheet (goo.gl/2LeXwM), where I try to calculate the principal outstanding but for some reason it just returns a zero. I have tried every different permutation and combination but nothing seems to work with the array function. I ofcourse have the option to do a classic 60 (5 year) row X 60 column table, but there are many products and it just becomes cumbersome.
    The way that the array needs to work is for each month do the following math (assuming its 3rd month)

    {sum(ppmt(rate loan 1, {1,2,3},loan period loan 1, value of loan 1)}
    +
    {sumproduct(ppmt(rate loan 2, {1,2},loan period loan 2, value of loan 2)}
    +
    {sumproduct(ppmt(rate loan 3, {1},loan period loan 3, value of loan 3)}

    and so on and so forth….

    In the attached excel sheet C2toC8 are details of the loan issued by firm A (Loan-1) and the cost of fund for the money it borrowed, D2toD8 are details of the loan issued and money borrowed in second month (Loan 2).

    Can someone please look at the array function and help me sort out the math for this.

    Thanks

    Akshay

  7. Joan says:

    Nice post!
    Have you posted any stuff explaining how to read/write constant arrays to/from vba?
    Thanks
    Joan

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s