Forum Discussion

amit_bhola's avatar
amit_bhola
Iron Contributor
Aug 07, 2021

Array Formulas / Array Concepts Summary (desired and attempted)

Array formulas / formulas dealing with arrays are great but hard to learn for an average user. Unfortunately, a comprehensive and formal description of rules and Excel behaviours regarding array form...
excel
Formulas and Functions
training
lori_m's avatar
lori_m
Iron Contributor
Aug 08, 2021

amit_bhola 

I'm not sure if there are any good resources which document the rules and Excel behaviours governing array formulas more reliably than those you have listed in the post. My understanding, based on developing function libraries in the Excel C API, is that array formula behavour is essentially dictated by the function signature.

 

Worksheet function parameters and results can be assigned a data type that may be a single value, array or reference. Examples of each type are given in the table below. Note some functions like INDEX may take either ranges or arrays as parameters or results.

 

Param / Result | Value          Array            Reference
----------------------------------------------------------
Value          | ABS(_)         INDEX(_,,_)      INDIRECT(_)
Array          | SUM(_)         TRANSPOSE(_)     INDEX(_,)
Reference      | N(_)           ROW(_)           OFFSET(_,,)

 

Underscores may be replaced with range or arrays and results analysed with the insert function dialog or evaluate function tools. Depending on Excel version, coercion may be required to obtain an array result for some of these functions. .

 

This link may provide further insight:
https://techcommunity.microsoft.com/t5/excel/hyperlink-function-do-not-support-dynamic-arrays/m-p/2028453
Comprehensive function listings by data type and further links are here:

https://chandoo.org/forum/threads/excel-function-reference.35686/
https://github.com/lhem/excel/blob/master/FuncList.xlsx

Resources