Array Formulas / Array Concepts Summary (desired and attempted)

Question

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 formulas is not well documented or not easily available. There are websites which try to explain things and Microsoft's own support pages

explain some guidelines but overall, the information is scattered. Some books can be found dedicated to Array Formulas, but a book is too lengthy an investment in terms of time at least, and is a last resort.

1) If any good resources exists which comprehensively document the rules and Excel behaviours governing array formulas in a summarised way, pls. share!

2) In a personal bid to summarise the common rules on a single page with demo examples, i have created attached file. The focus is to document how the array formulas / formulas dealing with arrays behave when,

a) The Inputs are : a range constant / an array constant / range from a formula / array from a formula ...

b) Given to : formulas which return an array / a single value ...

c) Typed in : single cell / multiple cells ...

d) Making use of : no operator / @ operator / CSE (ctrl+shift+enter).

The file is a personal work for personal use, but i felt that it can be of some use to other members here as a reference (only). Expert members are welcome to review it, correct it, value add, because it still seems incomplete and hence this post. Their inputs can help the community.

Yes, the attached file summary looks bit congested but it was a goal to give a single glance perspective to things. I hope it is not so complicated if each demo formula is seen one at a time, and by referring to the cell color which matches the rule it demonstrates. How the formulas work is not described in detail as one would do in a manual or training material because the file intent is neither of those. It is only to put in single place (for personal use) the common concepts taken from references included below.

Thanks,

References

https://support.microsoft.com/en-us/office/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7

https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531

https://support.microsoft.com/en-us/office/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34

https://exceljet.net/glossary/broadcasting

lori_m · Answer

amit_bhola&nbsp;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.&nbsp;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.&nbsp;Param / Result | Value          Array            Reference
----------------------------------------------------------
Value          | ABS(_)         INDEX(_,,_)      INDIRECT(_)
Array          | SUM(_)         TRANSPOSE(_)     INDEX(_,)
Reference      | N(_)           ROW(_)           OFFSET(_,,)&nbsp;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. .&nbsp;This link may provide further insight:https://techcommunity.microsoft.com/t5/excel/hyperlink-function-do-not-support-dynamic-arrays/m-p/2028453Comprehensive 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

peterbartholomew1 · Answer

amit_bhola&nbsp;I suspect you have taken on too much in trying to present a complex topic with a large number of exceptions in a single table.&nbsp; You will have benefitted in its production but, for someone looking for information, it makes for difficult reading.&nbsp;The first thing I would suggest is to deal with Excel 365 and legacy spreadsheets separately; they are very different beasts despite the fact that 365 is backward compatible.&nbsp; I would also recommend developing the ideas using mainstream functions before addressing the 'odd-ball' functions such as INDIRECT.&nbsp; There,&nbsp;lori_m's work on function signature should be valuable.&nbsp;One function that you could use is XLOOKUP which is capable of returning a range reference or an array (as is INDEX).&nbsp; As an example, the returned values of= XLOOKUP({"Feb";"March"}, textRange, numericRange)
and
= XLOOKUP("Feb", textRange, numericRange):XLOOKUP("March", textRange, numericRange)may look similar but nesting within ISREF( ) or using it within COUNTIFS( )= COUNTIFS(
    XLOOKUP({"Feb";"March"},textRange,numericRange),
    "&gt;5")

= COUNTIFS(
    XLOOKUP("Feb", textRange, numericRange):XLOOKUP("March",textRange,numericRange), 
    "&gt;5")gives very different outcomes.p.s. The names textRange and numericRange are simply your ranges held within cells A1:B4, but I haven't used direct referencing since 2015.

amit_bhola · Answer

PeterBartholomew1&nbsp;,&nbsp;lori_m&nbsp;&nbsp;Thanks for your insights and resources.&nbsp;I understand that there is always some chance of encountering exceptions in behaviours and then the pre vs. post Dynamic Arrays Excel changes.. there are many dimensions to cover. So it is just too ambitious (perhaps futile) an attempt to try to put everything on one screen and still keep it sensible/legible/clear to understand.&nbsp;The other forum discussion shared by you (which not coincidentally was an active discussion between you!) needs quite some time to go through. My work keeps me busy, so i hope to follow up at some time in future.&nbsp;Thanks,&nbsp;PS : For the HYPERLINK function post for which lori_m&nbsp;provided a link, i found using the Evaluate Function tool that the formula arguments are calculated in different order when implicit intersection operator @ is used vs. not used in one of the range arguments. That might explain why his solution worked internally. I am yet to come across a documentation which mentions this. If it is really so, then pages like below support page might need an update(?) with advent of DAEhttps://support.microsoft.com/en-us/office/the-order-in-which-excel-performs-operations-in-formulas-28eaf0d7-7058-4eff-a8ea-0a835fafadb8&nbsp;=HYPERLINK(A1:A3,B1:B3)=HYPERLINK(@A1:A3,B1:B3)&nbsp;&nbsp;&nbsp;&nbsp;

lori_m · Answer

amit_bhola&nbsp;It seems the evaluate formula tool skips over single cell references which can be confusing. One option is to put parentheses around arguments to access the intermediate results. This was mentioned in an example in some wiki notes accompanying the previous link,&nbsp;https://github.com/lhem/excel/wiki/Functions&nbsp;PeterBartholomew1I just noticed I also used the XLOOKUP function as an example in the preceding link and, indeed, since a single value is expected for first parameter the result of those formulas is an array of references which is not supported. This is a similar situation to the HYPERLINK example - I suppose now one may use the MAP function as a workaround without relying on relative referencing.

peterbartholomew1 · Answer

lori_m&nbsp;"now one may use the MAP function as a workaround without relying on relative referencing"I see it as more than a workaround.&nbsp; It should mean that now, at last, I can ditch the idea of relative referencing that I have regarded as a crap concept for a number of years!&nbsp;I have toyed with putting together am 'Excel for heretics' course in which the A1 (and R1C1) notations are introduced as legacy methods of referencing individual records of a table/list or elements of an array (an array being an ordered list addressed by index) before deprecated further use in 365.&nbsp;Quite often, the use of MAP is unnecessary because, with pairwise lifting, the same result is achieved with an array formula.&nbsp; Overall, I am very happy with the new helper functions.&nbsp; I put together an amortisation table with interest rates that can vary from year to year:= SCAN(principal, period#,
      LAMBDA(balance,p,
         LET(
            MPR, INDEX(rate#, p),
            remaining, 12*duration + 1 - p,
            flow, PMT(MPR, remaining, balance),
            (1+MPR)*balance+flow
         )
      )
   )and then summarised the results by year= BYROW(INDEX(interest#, SEQUENCE(duration,12)), LAMBDA(a,SUM(a)) )All far more elegant that direct relative cell referencing!

Forum Discussion

Array Formulas / Array Concepts Summary (desired and attempted)

7 Replies

Resources