Array Formula Concept Questions from M Girvin for Holy Macro! Book

[mgirvin]

Dear Smartest Excelers In The World.

I am writing a book for Holy Macro! Books Publishing about Array Formulas. I am making this post in hopes that I could get some feedback about some array formula concepts. Below are five questions that I have for all of you, the smartest Excel people that I know. If you would like to help me refine these ideas, please post a response. Thanks a million!

Question #1: Is this the definition of an array formula:
An array formula is a formula that requires Ctrl + Shift + Enter.

Question #2: Are these array formulas:
=IF(ROWS(G$8:G8)>$F$3,"",INDEX(A$6:A$13,AGGREGATE(15,6,(ROW($A$6:$A$13)-ROW($A$6)+1)/(($A$6:$A$13>=$B$3)*($A$6:$A$13<=$C$3)*($B$6:$B$13=$D$3)),ROWS(G$8:G8))))
=SUMPRODUCT(--ISNUMBER(MATCH(B9:B518,D11:D16,0)))
=SUMPRODUCT(--(FREQUENCY(A2:A5001,A2:A5001)>0))
=INDEX(LINEST($B$2:$B$12,$A$2:$A$12),ROWS(E$5:E5))
=LOOKUP($E4&F$3,$A$3:$A$16&$B$3:$B$16,$C$3:$C$16)

Question #3: Are these the only Array Functions in Excel:
FREQUENCY, LINEST, MINVERSE, MMULT, MODE.MULT (Excel 2010), MUNIT (Excel 2013), TRANSPOSE, TREND.

Question #4:
If the IF function logical-test argument contains an array calculation it requires Ctrl + Shift + Enter no matter where it is located in the larger formula. For example, if the IF function logical-test argument contains an array calculation and the IF function is located in the array1 argument of SUMPRODUCT, the formula will require Ctrl + Shift + Enter. Do you know any other functions that display this behavior? Do you know why the IF function has this behavior, but so many others do not have this behavior?

Question #5:
In trying to define an array formula, I have come up with this long definition and set of rules. What is your opinion on what I have written?

Definition of array formula:

An array is a collection of 2 or more items.

There are three types of arrays that you can have in Excel:

1. Reference that contains more than one cell. Like a range of cells, worksheet reference or a Defined Name.
2. Array created by a formula element. Also called “resultant array”. This is the array of items created by the array operation.
3. Array constant. An array of values hard coded into the formula.

An array formula is a formula that contains an operation on an array of items rather than a single item. Operations include: math, comparative, join, function argument. Array operations in array formulas produce a resultant array (more than one item); this is different from an operation on an array of values such as =SUM(A1:A5) which produces an aggregate calculation and a single item as its result. The resultant array can be the final result of an array formula or it can be used as a formula element in a larger formula. The final result from an array formula can either be a single value or an array of values.
Note: An exception to this definition is: if you highlight the range A1:A5 and type a formula in the Active Cell like: ={1;2;3;4;5}, or =43, or =”Love” and then use the keystrokes Ctrl + Shift + Enter, you will create a “sort of array formula”. The range A1:A5 will contain a formula with curly braces that will act as a single unit and disallow any one part to be deleted. However, the formula contains no direct array operation (math, comparative, join, function argument); it’s only “array” operation is to use the keystrokes Ctrl + Shift + Enter to enter the formula into multiple cells.

Operators are:

1. Math: -, +, *. /, ^
2. Comparative: =, <>, >, >=, <, <=
3. Join: &
4. Function argument:
   • If you give a function argument that expects a single item a single item, the function will deliver a single answer.
   • If you give a function argument that expects a single item more than one item, the function will deliver more than one answer. Specifically, if you give the function argument n items, the function will deliver n answers.
   • When the function argument is expecting a single item and you give it more than one item, you have created a function argument array operation.

Rules for entering a single cell array formula into a cell:

1. If the function argument does not innately handle array operations and we give it an array operation, we must enter the formula with Ctrl + Shift + Enter. The MAX function number1 argument is an example of an argument that does NOT innately handle array operations and therefore requires Ctrl + Shift + Enter when you place an array operation into the number1 argument. The SUMPRODUCT array1 argument is an example of a function that DOES innately handle array operations and therefore does NOT require Ctrl + Shift + Enter when you place an array operation into the array1 argument.
2. After we enter a formula with Ctrl + Shift + Enter, Excel places curly braces at the beginning and end of the formula. This is put in by Excel, not by you. If you try to type these curly braces in, they will appear as text, not as part of an array formula.
3. If we don’t use Ctrl + Shift + Enter for a formula that requires it, we will get a #VALUE! error or a potentially incorrect answer from implicit intersection.
4. Caveats:
   • Array operations on array constants usually do not require Ctrl + Shift + Enter.
   • IF function array calculations will always require Ctrl + Shift + Enter even if they are placed in function arguments like array1 for SUMPRODUCT which can innately handle array operations.
   • If you create a Defined Name that contains a formula that requires Ctrl + Shift + Enter, and then place that Defined Name in a cell formula, the formula will not require Ctrl + Shift + Enter.

The following five function arguments can handle arrays without having to enter the array formula with the keystrokes Ctrl + Shift + Enter:

1. The array_1, array_2, etc. arguments in SUMPRODUCT
2. lookup_vector argument in the LOOKUP function.
3. result_vector argument in the LOOKUP function.
4. The array argument in INDEX
5. The array argument in AGGREGATE for functions 14 to 19

Rules for array constants:

1. Array constants can be used in array and non-array formulas.
2. Array Syntax rules:
   • Curly braces house the array: one at the beginning and one at the end.
   • Semi-colon means “go down a row”.
   • Commas mean “go over a column”.
   • Text items are contained in double quotes.
   • Numbers, Logical values and error values are not contained in two double quotes.
   • The three types of array constants are: column (vertical), row (horizontal), or table (two-way).
3. Array constants are limited by the number of characters allowed in a formula (8,192).
4. For array formulas:
   • If the array operation contains arrays that are array constants and no other type of an array (like a range of cells), the formula should not require Ctrl + Shift + Enter.
   • Examples we have seen (in full text from book) of function arguments that can contain an array constant as part of an array operation and will not require Ctrl + Shift + Enter are: array in SMALL, array in LARGE, start_num in MID, logical in OR, number1 in PRODUCT, number1 in SUM.
   • Examples we have seen of function arguments that WILL require Ctrl + Shift + Enter if they contain an array constant: col_index_num argument in VLOOKUP.

Rules for array formulas that deliver more than one item to more than one cell:
There are two types of array formulas that can simultaneously deliver more than one value to more than one cell using Ctrl + Shift + Enter:

1. Array formulas that you create with various formulas elements and put together yourself.
2. Built-in Excel Array Functions.

Rules:

1. Select a range of cells, create formula/function, and then enter with Ctrl + Shift + Enter.
2. The array formula/function is considered a single unit and structural changes such as deleting individual cells, deleting individual cell content or inserting rows in disallowed.
3. The keyboard shortcut for highlighting the Current Array (all the cells that are part of the array formula) is: Ctrl + / (forward slash).
4. If you want to delete the array formula/function, you can highlight the entire array formula (Ctrl + /) and then use the delete key. Another method for deleting array formula is to select any one cell, then hit the backspace key, and then Ctrl + Shift + Enter.
5. If you need to edit your array formula, edit any one particular cell in the array and then re-enter the array formula with Ctrl + Shift + Enter.
6. When you enter an array formula/function into more than one cell using Ctrl + Shift + Enter, the cell references will be the same in each cell regardless if the cell references in the active cell are locked or not locked (absolute or relative).
7. Array Functions (except for the TRANSPOSE function) can be placed in other functions that expect a range of values (like COUNT, MIN, MAX) and the formula should not require Ctrl + Shift + Enter.
8. A formula like: ={1;2;3;4;5}, or =43, or =”Love” entered into the range A1:A5 with Ctrl + Shift + Enter is an array formula with no direct array operation. It will show curly braces and will act as a single unit.

Thanks very much for looking through these array formula notes!