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.
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?
Thank you so much for the reply and the excellent work you present. REDUCE was the next thing I was going to try but only after I had hit a dead end with MAP. I decided to pause to see what ideas you and others might have (and it was coming up to midnight).
I can understand that support for 'arrays of arrays' is out of scope for Excel, but should it do more with what it has. The idea that a vertical array of row arrays/ranges should map to a 2D array (with a specified fill characters for unused cells) would not appear to be that revolutionary; after all the implementations of H- and VSTACK that I have seen go much further.
The problem with the blank row is probably something that should be addressed within VSTACKλ but I wrote it in a hurry to replace Charles Williams's VSTACK function. It would be perfectly reasonable to assume that, should an array be identifiable as 'null', it may be stacked to leave the second array unaltered. Your test has some class, though a slightly 'techie' for a spreadsheet solution.
lori_m Sorry, I meant to include you within the OP but couldn't locate your user id.
PeterBartholomew1 The FastExcel xll you provided with the ACCUMULATE example seems to give me access to the other functions as well (intended or not??), but without the user interface bells and whistles. I started trying to replicate a bunch of those functions with LAMBDA and the new helpers (with the help of the online PDF user guide), but haven't gotten to his _STACK functions just yet; working through DIFF and sent me on a tangent to be able to handle some of its nuances more cleanly.
As you say, it could be handled in STACK just as well as in the REDUCE LAMBDA call, but I think having the inclusive vs. exclusive qualities of REDUCE/SCAN like were in my pre-helper fold/reduce/scan versions would've been nice, though there are probably quite goods reasons why its not set up that way.
