Forum Discussion
A statement of truth or a wasted opportunity. Lambda helper functions reluctant to return 2D arrays.
I got a surprise playing with SCAN in response to a question on this forum. Basically the OP wanted to decrease an amount by 10% per day, thus giving rise to a geometric sequence. Besides calculating the sequence directly, I also used SCAN to give the result array in a manner that could accommodate a table of variable rates.
Then I turned the percentage into an array {10%,12%} to see what happens, fully expecting SCAN to moan about nested arrays. What happened was that the direct calculation of geometric sequences gave two sequences, one for 10% and the other 12%, but scan did something entirely different.
What it did was to apply an alternating pattern of 10% and 12% as a single calculation! The challenge might be to think up a meaningful use case for such a calculation.
MicrosoftSCAN iterates through the provided array argument in row-major order and returns its output in the same shape as the array argument. So this behavior is expected, array-of-array are not encountered as each iteration of SCAN only returns a scalar.
I was getting there slowly by trial and error, but it is good to have the behaviour confirmed. It has a logical basis for understanding though I can think of few cases where I would be likely to use the row-major order. Maybe an escalation calculation where the days are organised by weekday; continuity as one moves down a week could make sense.
Maybe scanning an array with multiple initial values would require separate functions of VSCAN and HSCAN? In business-based calculation, I suspect that most scanned accumulations would normally be conducted in parallel, but in engineering dynamics there are likely to be multiple position and velocity values required to capture the state of a system and there will be coupling between the system coordinates from step to step. I wanted to check the simulation of a container port crane moving a load that is free to swing as a pendulum.
I gave up on that (it requires 4 values), but have attached a worksheet following the motion of a single object. That only required one position x(t) and the corresponding velocity v(t) to be tracked. Because SCAN only works the way I need over a single column, I combined the two values as a complex number for the purpose of the calculation. The job of the Lambda function is to split the string into two numbers, to calculate the two in a way that takes account of the current velocity forces acting on the system (restoring force, control force and damping) and then uses the COMPLEX function to recombine the two forces into one.
= SCAN("2i", F#, LAMBDA(coord,F₀, LET( x₀, IMREAL(coord), v₀, IMAGINARY(coord), x₁, x₀ + Δt*v₀, v₁, v₀ + Δt*F₀/Mass - Δt*Coef*v₀-κ*x₀, COMPLEX(x₁,v₁) ) ) )The result is generated as a complex number which is absolutely unintelligible, so I split real and imaginary parts (position and velocity) for presentation. I then hit the problem that IMREAL and IMAGINARY do not accept array arguments, so I have to use MAP. Map will only transform one column at a time so there are two distinct applications of MAP, which I then combine into an array.
= MAP(p#, LAMBDA(z, IMREAL(z))) = MAP(p#, LAMBDA(z, IMAGINARY(z))) = IF({1,0},x#,v#)There is a lot of tidying up I could do, but VSCAN (or equivalent solution) would be great sometime in the future.
Meanwhile there is a lot to learn concerning how best to use what we have. For example, is it good to leave the anonymous Lambda functions at the point at which they are used; is it better to use a LET wrapper to define a local function so that it is more meaningful at the point of application. A step further and one hides all the working within Name Manager.
PeterBartholomew1Revisited your particle dynamics and reworked so that the values are passed in LAMBDA function rather than as COMPLEX, taking advantage of what we've learned about arrays of LAMBDAs.
=LET( pairs, SCAN(LAMBDA(x,CHOOSE(x,0,2)), F#, LAMBDA(coord,F₀, LET( x₀,coord(1), v₀, coord(2), x₁, x₀ + Δt*v₀, v₁, v₀ + Δt*F₀/Mass - Δt*Coef*v₀-κ*x₀, LAMBDA(x,CHOOSE(x,x₁,v₁)) ) ) ), x,MAP(pairs,F#,LAMBDA(fn,ignored,fn(1))), v,MAP(pairs,F#,LAMBDA(fn,ignored,fn(2))), CHOOSE({1,2},x,v) )Sooner or later, I may try to generalise your formulae to cover more degrees of freedom.
First, however, I think I need to take time out to review the progress we (mainly you) have made. I need to analyse the lessons learnt and would like to be sure I can apply them when the situation arises.
PeterBartholomew1I thought I'd mock this up using the proto-CORKSCREW function I had put together previously; in terms of efficiency, I think your use of COMPLEX to pass a tuple in SCAN is probably better than this, since it does seem to sputter after hitting enter.
=REDUCE( {0,-0.1,1,0,2}, SEQUENCE(N), 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( k,get_prior(1)+1, t_,get_prior(2)+Δt, F_t,XLOOKUP(t_,{0;1;4;6},{1;0;1;0},,-1), x_t,get_prior(4)+Δt*get_prior(5), v_t,get_prior(5)+Δt*F_t/Mass-Δt*Coef*get_prior(5)-κ*get_prior(4), CHOOSE(j,k,t_,F_t,x_t,v_t) ) ) ) ) ) ) )I have continued to explore areas of difficulty in implementing dynamic array solutions that Lambda functions may solve. The first is habit of aggregation functions to devour entire arrays rather than working through them term by term.
Following your lead, I used named formulas defined locally within a LET function. The aggregator was the MEDIAN function which can be useful when one needs a ramp function, here used to calculate overlapping intervals (could be overtime calculation, marginal tax rates, bonus levels etc.)
The formula is somewhat on the heavy side
= LET( \0, "Define Lambda function", OVERLAPλ, LAMBDA(lower,upper, LAMBDA(start,end, MEDIAN(end, lower, upper) - MEDIAN(start, lower, upper) )), \1, "Apply function", MAP(start, end, OVERLAPλ(lowerThreshold, upperThreshold)) )but it works OK. Despite that, it is not easily assimilated as a worksheet formula (I can see most users looking on in blank incomprehension or running for the hills!) Loading the Lambda function to a defined name helps the worksheet, but at the expense of obscuring the definition of the Lambda function.
= MAP(start,end, OVERLAPλ(lowerThreshold,upperThreshold))An idea that I came up with was a GLOBAL function that would operate like LET but also upload the local name to the workbook names collection.
= GLOBAL( \0, "Define Lambda function", OVERLAPλ, LAMBDA(lower,upper, LAMBDA(start,end, MEDIAN(end, lower, upper) - MEDIAN(start, lower, upper) )), \1, "Include comment for upload", "OVERLAPλ(lowerThreshold,upperThreshold)(start,end)" )Do you think such a function would be useful, or is it an idea to be consigned to the crazy bin?
The other problem I addressed within the workbook is that of the ...IFS family of conditional aggregations expecting a Range reference, so they will not accept functions of the criteria arrays as parameters. The alternative strategy of FILTER and aggregate is fine but, by its nature, will not accept an array of criteria values. Enter MAP. The model aggregates by day of the week, which could also be achieved by using a helper column.
I am not an engineer - any mechanics courses I might have taken are a distant memory now! That said, I am interested by the analysis and will make a note to review this once these functions go mainstream. One minor observation: perhaps one could insert a + instead of using MAP? i.e.
=IMREAL(+p#)
=IMAGINARY(+p#)VSCAN/HSCAN were mentioned in some Microsoft Research posts I remember. My suggestion was that SCAN might be able to subsume these two cases so in your example one could make the substitution initial -> Initial*{1,1} . I haven't thought through the implications fully so not sure if that would be an improvement or not.
I also had in mind combining REDUCE with least squares functions like LINEST (e.g. Gauss-Newton method) that would involve a degree of 'coupling' and which has curve fitting applications in finance.
I should have thought of that! The formula
= IF({1,0}, IMREAL(+p#), IMAGINARY(+p#))works fine. I am used to the tactic for EMONTH and QUOTIENT (though MOD doesn't require it). Another strategy would be to make 'p' a named formula rather than a spilt range, which tidies up the workbook, though somewhat at the expense of transparency.
I think you ideas for extending the scope of SCAN sound workable from a user perspective. I come up against the problem quite often but, then, I doubt many other users are making such a determined effort to get rid of relative referencing from Excel solutions as an undesirable concept. The main exception is within a table where the structured reference [@Column1] is still needed and meaningful in context.
