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:
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:
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.
Thank you for the great explanation.
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
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.
@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”}
Sorry mRng = Sheet1!$A$1:$C$1
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.
Colin,
Thats interesting: I had not realised that implicit intersection worked in those circumstances.
Pingback: Excel Roundup 20150119 « Contextures Blog
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
Nice post!
Have you posted any stuff explaining how to read/write constant arrays to/from vba?
Thanks
Joan