Discussion Re: Array Formulas / Array Concepts Summary (desired and attempted) in Excel
https://techcommunity.microsoft.com/t5/excel/array-formulas-array-concepts-summary-desired-and-attempted/m-p/2671641#M111101
<P><LI-USER uid="288074"></LI-USER> </P><P>I was aware of <LI-USER uid="9664"></LI-USER>'s presentation and had found it added clarity to some of the non-standard techniques I had adopted in legacy Excel. I had schooled myself to commit all formulas with CSE but it hardly provided a great user experience. What I had discovered was that moving the calculation to name manager simplified the use of sequences of array formula (despite an MS help page that suggested that the uses of named functions within names was not recommended).</P><P> </P><P>The insight, given by the presentation, that I was simply using defined Names to evade implicit intersection, was really helpful. The dynamic array behaviour that emerged was better than anything I had hoped for. I hadn't got beyond the point of 'Insert Array' (to parallel 'Insert Table') in my thinking but allowing array calculation to roam free was way better.</P>Sat, 21 Aug 2021 10:59:23 GMTPeter Bartholomew2021-08-21T10:59:23ZArray Formulas / Array Concepts Summary (desired and attempted)
https://techcommunity.microsoft.com/t5/excel/array-formulas-array-concepts-summary-desired-and-attempted/m-p/2623809#M109631
<P>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 </P><P>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.</P><P> </P><P>1) If any good resources exists which <STRONG>comprehensively</STRONG> document the rules and Excel behaviours governing array formulas in a summarised way, pls. share!</P><P> </P><P>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,</P><P> </P><P>a) The Inputs are : a range constant / an array constant / range from a formula / array from a formula ...</P><P>b) Given to : formulas which return an array / a single value ...</P><P>c) Typed in : single cell / multiple cells ...</P><P>d) Making use of : no operator / @ operator / CSE (ctrl+shift+enter).</P><P> </P><P>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.</P><P> </P><P>Yes, the attached file summary looks bit congested but it was a goal to give a <EM>single glance</EM> 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 <SPAN>below.</SPAN></P><P> </P><P>Thanks,</P><P> </P><P>References</P><P><A href="https://support.microsoft.com/en-us/office/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7" target="_blank" rel="noopener">https://support.microsoft.com/en-us/office/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7</A></P><P> </P><P><A href="https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531" target="_blank" rel="noopener">https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531</A></P><P> </P><P><A href="https://support.microsoft.com/en-us/office/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34" target="_blank" rel="noopener">https://support.microsoft.com/en-us/office/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34</A></P><P> </P><P><A href="https://exceljet.net/glossary/broadcasting" target="_blank" rel="noopener">https://exceljet.net/glossary/broadcasting</A></P>Sat, 07 Aug 2021 19:19:59 GMThttps://techcommunity.microsoft.com/t5/excel/array-formulas-array-concepts-summary-desired-and-attempted/m-p/2623809#M109631amit_bhola2021-08-07T19:19:59ZRe: Array Formulas / Array Concepts Summary (desired and attempted)
https://techcommunity.microsoft.com/t5/excel/array-formulas-array-concepts-summary-desired-and-attempted/m-p/2624734#M109658
<P><LI-USER uid="636579"></LI-USER> </P><P>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.</P><P> </P><P>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.</P><P> </P><LI-CODE lang="applescript">Param / Result | Value Array Reference
----------------------------------------------------------
Value | ABS(_) INDEX(_,,_) INDIRECT(_)
Array | SUM(_) TRANSPOSE(_) INDEX(_,)
Reference | N(_) ROW(_) OFFSET(_,,)</LI-CODE><P> </P><P>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. .</P><P> </P><P>This link may provide further insight:<BR /><A href="https://techcommunity.microsoft.com/t5/excel/hyperlink-function-do-not-support-dynamic-arrays/m-p/2028453" target="_blank">https://techcommunity.microsoft.com/t5/excel/hyperlink-function-do-not-support-dynamic-arrays/m-p/2028453</A><BR />Comprehensive function listings by data type and further links are here:</P><P><A href="https://chandoo.org/forum/threads/excel-function-reference.35686/" target="_blank">https://chandoo.org/forum/threads/excel-function-reference.35686/</A><BR /><A href="https://github.com/lhem/excel/blob/master/FuncList.xlsx" target="_blank">https://github.com/lhem/excel/blob/master/FuncList.xlsx</A></P>Sun, 08 Aug 2021 12:31:26 GMThttps://techcommunity.microsoft.com/t5/excel/array-formulas-array-concepts-summary-desired-and-attempted/m-p/2624734#M109658lori_m2021-08-08T12:31:26ZRe: Array Formulas / Array Concepts Summary (desired and attempted)
https://techcommunity.microsoft.com/t5/excel/array-formulas-array-concepts-summary-desired-and-attempted/m-p/2625203#M109687
<P><LI-USER uid="636579"></LI-USER> </P><P>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. You will have benefitted in its production but, for someone looking for information, it makes for difficult reading.</P><P> </P><P>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. I would also recommend developing the ideas using mainstream functions before addressing the 'odd-ball' functions such as INDIRECT. There, <LI-USER uid="288074"></LI-USER>'s work on function signature should be valuable.</P><P> </P><P>One function that you could use is XLOOKUP which is capable of returning a range reference or an array (as is INDEX). As an example, the returned values of</P><LI-CODE lang="excel-formula">= XLOOKUP({"Feb";"March"}, textRange, numericRange)
and
= XLOOKUP("Feb", textRange, numericRange):XLOOKUP("March", textRange, numericRange)</LI-CODE><P>may look similar but nesting within ISREF( ) or using it within COUNTIFS( )</P><LI-CODE lang="excel-formula">= COUNTIFS(
XLOOKUP({"Feb";"March"},textRange,numericRange),
">5")
= COUNTIFS(
XLOOKUP("Feb", textRange, numericRange):XLOOKUP("March",textRange,numericRange),
">5")</LI-CODE><P>gives very different outcomes.</P><P><EM>p.s. The names </EM>textRange <EM>and </EM>numericRange <EM>are simply your ranges held within cells </EM>A1:B4<EM>, but I haven't used direct referencing since 2015.</EM></P>Sun, 08 Aug 2021 16:34:41 GMThttps://techcommunity.microsoft.com/t5/excel/array-formulas-array-concepts-summary-desired-and-attempted/m-p/2625203#M109687Peter Bartholomew2021-08-08T16:34:41ZRe: Array Formulas / Array Concepts Summary (desired and attempted)
https://techcommunity.microsoft.com/t5/excel/array-formulas-array-concepts-summary-desired-and-attempted/m-p/2625411#M109690
<P><LI-USER uid="214174"></LI-USER> , <LI-USER uid="288074"></LI-USER> </P><P> </P><P>Thanks for your insights and resources.</P><P> </P><P>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.</P><P> </P><P>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.</P><P> </P><P>Thanks,</P><P> </P><P>PS : For the HYPERLINK function post for which <LI-USER uid="288074"></LI-USER> provided a link, i found using the Evaluate Function tool that the formula arguments are calculated in different <EM>order</EM> when implicit intersection operator @ is used vs. not used in one of the range arguments. That might explain why his solution worked <EM>internally</EM>. 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 DAE<BR /><A href="https://support.microsoft.com/en-us/office/the-order-in-which-excel-performs-operations-in-formulas-28eaf0d7-7058-4eff-a8ea-0a835fafadb8" target="_blank">https://support.microsoft.com/en-us/office/the-order-in-which-excel-performs-operations-in-formulas-28eaf0d7-7058-4eff-a8ea-0a835fafadb8</A></P><P> </P><LI-CODE lang="excel-formula">=HYPERLINK(A1:A3,B1:B3)</LI-CODE><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="amit_bhola_0-1628448840710.png" style="width: 999px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/301643i4B49748D62F61202/image-size/large?v=v2&px=999" role="button" title="amit_bhola_0-1628448840710.png" alt="amit_bhola_0-1628448840710.png" /></span></P><LI-CODE lang="excel-formula">=HYPERLINK(@A1:A3,B1:B3)</LI-CODE><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="amit_bhola_1-1628448907853.png" style="width: 999px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/301644i084F7D8310AB0B82/image-size/large?v=v2&px=999" role="button" title="amit_bhola_1-1628448907853.png" alt="amit_bhola_1-1628448907853.png" /></span></P><P> </P><P> </P><P> </P>Sun, 08 Aug 2021 19:03:34 GMThttps://techcommunity.microsoft.com/t5/excel/array-formulas-array-concepts-summary-desired-and-attempted/m-p/2625411#M109690amit_bhola2021-08-08T19:03:34ZRe: Array Formulas / Array Concepts Summary (desired and attempted)
https://techcommunity.microsoft.com/t5/excel/array-formulas-array-concepts-summary-desired-and-attempted/m-p/2625631#M109703
<P><LI-USER uid="636579"></LI-USER> </P><P>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, <A href="https://github.com/lhem/excel/wiki/Functions" target="_blank" rel="noopener">https://github.com/lhem/excel/wiki/Functions</A></P><P> </P><P><LI-USER uid="214174"></LI-USER></P><P>I 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.</P>Sun, 08 Aug 2021 22:26:06 GMThttps://techcommunity.microsoft.com/t5/excel/array-formulas-array-concepts-summary-desired-and-attempted/m-p/2625631#M109703lori_m2021-08-08T22:26:06ZRe: Array Formulas / Array Concepts Summary (desired and attempted)
https://techcommunity.microsoft.com/t5/excel/array-formulas-array-concepts-summary-desired-and-attempted/m-p/2627158#M109752
<P><LI-USER uid="288074"></LI-USER> </P><P><SPAN>"now one may use the MAP function as a workaround without relying on relative referencing"</SPAN></P><P><SPAN>I see it as more than a workaround. 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!</SPAN></P><P> </P><P><SPAN>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.</SPAN></P><P> </P><P><SPAN>Quite often, the use of MAP is unnecessary because, with pairwise lifting, the same result is achieved with an array formula. Overall, I am very happy with the new helper functions. I put together an amortisation table with interest rates that can vary from year to year:</SPAN></P><LI-CODE lang="excel-formula">= 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
)
)
)</LI-CODE><P>and then summarised the results by year</P><LI-CODE lang="excel-formula">= BYROW(INDEX(interest#, SEQUENCE(duration,12)), LAMBDA(a,SUM(a)) )</LI-CODE><P>All far more elegant that direct relative cell referencing!</P>Mon, 09 Aug 2021 11:53:23 GMThttps://techcommunity.microsoft.com/t5/excel/array-formulas-array-concepts-summary-desired-and-attempted/m-p/2627158#M109752Peter Bartholomew2021-08-09T11:53:23ZRe: Array Formulas / Array Concepts Summary (desired and attempted)
https://techcommunity.microsoft.com/t5/excel/array-formulas-array-concepts-summary-desired-and-attempted/m-p/2669808#M111054
<P><LI-USER uid="214174"></LI-USER> </P><P>Indeed, my take is that MAP and SCAN fill a large gap in array calculation, allowing power users and developers to construct array solutions where DA evaluation falls short. I remember MAP was suggested in a discussion you initiated before LAMBDA even appeared on the scene. Perhaps there'll be a more compact approach if/when nested arrays are supported in future.</P><P> </P><P><LI-USER uid="636579"></LI-USER> <BR />In addition to the links you give above, I came across a nice overview by Joe McDaid,<BR /><A href="https://powerusers.microsoft.com/t5/2019-MSBizAppsSummit-Gallery/Microsoft-Excel-Advanced-spreadsheet-modeling-using-Dynamic/td-p/311468" target="_blank">https://powerusers.microsoft.com/t5/2019-MSBizAppsSummit-Gallery/Microsoft-Excel-Advanced-spreadsheet-modeling-using-Dynamic/td-p/311468</A></P><P> </P><P>Function signature is mentioned (29:15-32:10) and is fairly easy to understand in the context of new DA evaluation though reference types are not covered in that presentation (to keep things simple i suppose). Notable examples of functions taking reference parameters include the N function and former analysis toolpak functions. These functions can resolve an array of returns from OFFSET or INDIRECT though range parameters may need coercing to arrays to obtain required results.</P><P> </P><P>The rules for legacy implicit intersection evaluation are more complex. Functions which default to a scalar result are underlined in the previous function listing and in general require CSE whether or not the parameter is a range or an array. To deliver an array of results, one may pass an array/reference result as a reference parameter e.g. N(INDEX(_,)) so as to override implicit intersection evaluation of function parameters that may return arrays e.g. CELL(_). This latter behaviour is really not intuitive at all - early in the presentation even refers to old style formula combinations of this kind as 'Black Magic'!</P>Fri, 20 Aug 2021 16:02:28 GMThttps://techcommunity.microsoft.com/t5/excel/array-formulas-array-concepts-summary-desired-and-attempted/m-p/2669808#M111054lori_m2021-08-20T16:02:28ZRe: Array Formulas / Array Concepts Summary (desired and attempted)
https://techcommunity.microsoft.com/t5/excel/array-formulas-array-concepts-summary-desired-and-attempted/m-p/2671641#M111101
<P><LI-USER uid="288074"></LI-USER> </P><P>I was aware of <LI-USER uid="9664"></LI-USER>'s presentation and had found it added clarity to some of the non-standard techniques I had adopted in legacy Excel. I had schooled myself to commit all formulas with CSE but it hardly provided a great user experience. What I had discovered was that moving the calculation to name manager simplified the use of sequences of array formula (despite an MS help page that suggested that the uses of named functions within names was not recommended).</P><P> </P><P>The insight, given by the presentation, that I was simply using defined Names to evade implicit intersection, was really helpful. The dynamic array behaviour that emerged was better than anything I had hoped for. I hadn't got beyond the point of 'Insert Array' (to parallel 'Insert Table') in my thinking but allowing array calculation to roam free was way better.</P>Sat, 21 Aug 2021 10:59:23 GMThttps://techcommunity.microsoft.com/t5/excel/array-formulas-array-concepts-summary-desired-and-attempted/m-p/2671641#M111101Peter Bartholomew2021-08-21T10:59:23Z