Forum Discussion
Array Formulas / Array Concepts Summary (desired and attempted)
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, https://github.com/lhem/excel/wiki/Functions
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.
"now one may use the MAP function as a workaround without relying on relative referencing"
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!
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.
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:
= 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!
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.
amit_bhola
In addition to the links you give above, I came across a nice overview by Joe McDaid,
https://powerusers.microsoft.com/t5/2019-MSBizAppsSummit-Gallery/Microsoft-Excel-Advanced-spreadsheet-modeling-using-Dynamic/td-p/311468Function 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.
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'!
I was aware of JoeMcDaid'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).
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.