Forum Discussion
Array of arrays using Lambda helper functions
PeterBartholomew1Just to get rid of that pesky blank row:
=REDUCE( "", ListCountry#, LAMBDA(acc,val, IF( TYPE(acc)<>64,TRANSPOSE(FILTER(StartList[ATHLETE],StartList[COUNTRY]=val)), VSTACKλ(,acc,TRANSPOSE(FILTER(StartList[ATHLETE],StartList[COUNTRY]=val))) ) ) )I think since MAP will iterate over an array of any shape, the results have to fit back into the same shape; I'd be interested to know if this is a mistaken assumption on my part.
The example I used was drawn from past work and refactored to provide an excuse to exercise the new helper functions, as announced by Chris_Gross . There might be justification to feed some ideas back but I didn't wish to be seen as trying to hijack a blog post, so I opened the new discussion.
I started with SCAN, basing it upon an amortisation schedule with occasional variation in interest rate
using the 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
)
)
)The other formula of interest was the aggregation of interest over one year periods to present in the summary by year.
= BYROW(INDEX(interest#,SEQUENCE(duration,12)), LAMBDA(a,SUM(a)) )That uses INDEX/SEQUENCE to convert the list to a crosstab and then the new BYROW to sum over the months of each year.
What I probably should feed back is the thought that the new helper functions address just about all of the frustrations I have had concerning Excel over the past years. Brilliant! Probably the thing that now comes to the fore is the need to revamp the user experience to make building, evaluating and debugging formulas a good experience. Neither the definition of names not the formula bar are really fit for purpose any more.
That should probably be the subject for separate discussion though.
PeterBartholomew1 Here's a proto-CORKSCREW attempt on your example; uses REDUCE and MAKEARRAY and some hard-coded values. Not sure how resource intensive these might be, but if you do alot of cashflows with same layout, it might suffice.
=REDUCE(
{0,"","","",100000},
SEQUENCE(5*12),
LAMBDA(acc,val,
LET(
get_prior,LAMBDA(x,INDEX(acc,ROWS(acc),x)),
MAKEARRAY(
ROWS(acc)+1,
COLUMNS(acc),
LAMBDA(i,j,
IF(
i<=ROWS(acc),INDEX(acc,i,j),
LET(
period,get_prior(1)+1,
prior_bal,get_prior(5),
rate_,(1+XLOOKUP(1+QUOTIENT(+period, 12), {0;4}, {0.08;0.1}, ,-1))^(1/12)-1,
MPR,IF(period>1,get_prior(2)),
flow,PMT(MPR,60+1-period,prior_bal),
end_bal,(1+MPR)*prior_bal+flow,
pay,prior_bal*(1+rate_)-end_bal,
CHOOSE(j,period,rate_,pay,prior_bal*rate_,end_bal)
)
)
)
)
)
)
)I am not sure that represents a direction of travel that I will be following! After all, I passed on Charles's CORKSCREW function and requested the more limited ACCUMULATE function. Nevertheless, it opens my eyes to the possibilities. It strikes me as pushing back the boundaries of the Excel formula as a program.
1. Data input is provided by full access to the workbook names
2. Multiple calculations are taken forward simultaneously
3. This is probably out of scope for SCAN so REDUCE and MAKEARRAY have been used instead (does this imply that the first steps are repeated for every subsequent time period?)
4. Results are output to a contiguous region, multiple arrays being represented within one block.
Have I got this right, or are there things I have misunderstood even at this stage?
Something I have been considering is 'to what extent should I use Names for a hierarchy of Lambda functions in order to modularise the code? I guess that is something you have done with your get_prior function though at first reading I simply read it as a variable name. I think I have some catching up to do. I have even got to decide on the objective; am I looking for the programming equivalent of the combined harvester or will a scythe and a ball of string do the job?
3. This is probably out of scope for SCAN so REDUCE and MAKEARRAY have been used instead (does this imply that the first steps are repeated for every subsequent time period?)
Based on what I've been able to determine, SCAN won't take an array as an init such that an array will be returned of more than one-dimension; I think BYROW/BYCOL/SCAN all have this limitation, but I'm not sure I understand why (other than "that's just how it is"); I've fiddled briefly with some of the "old-fashioned" coercion techniques to get them to produce larger arrays, but with no luck. As for the iterations, I'm assuming that the calcs are only performed for each new row and that the accumulator is just being referenced; I question whether the MAKEARRAY call for each new row is efficient, but I wonder if we'll get a native LAMBDA helper to replicate STACK, which is effectively what its doing here. I wonder if Chris_Gross or some one could provide some insight on what's going on under the hood so we can know better what is efficient or will slow a workbook down once I've got a hundred different calls in one.
Have I got this right, or are there things I have misunderstood even at this stage? >> Sounds right to me!
I have even got to decide on the objective; am I looking for the programming equivalent of the combined harvester or will a scythe and a ball of string do the job >> I think it could be both? I know having simple LAMBDA access could make data-cleaning easy, even more so with the new helpers. I think we fall back to the efficiency argument to determine the scale of the implements needed; will these type of REDUCE/MAKEARRAY combinations bog things down, and how many is too many?
My guess is that this is a positive decision to avoid situations that could potentially give rise to an 'array of arrays'. That is fine if there are active plans to support such data structures natively (we can wait) but, if not, defaulting to a padded 2D array would be a far better solution than throwing an error. Forcing you to use REDUCE / MAKEARRAY to achieve the obvious result is not satisfactory.
It isn't even as if such ideas are alien to Excel.