A statement of truth or a wasted opportunity. Lambda helper functions reluctant to return 2D arrays.

tboulden 

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.

tboulden 

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?

lori_m SergeiBaklan 

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.

Hi tboulden 

JoeMcDaid is probably holding his head in horror at the things these dammed users are putting his code through!  Put some good functionality out there and they take the Micky by nesting Lambda functions 5 deep in formulas that barely fit the screen.

I am picking up ideas from you though; the definition of the Lambda function 'get_prior( )' within LET as a locally-scoped function is something I have adopted. 

Your formulation would generalise to 4 problem variables better than mine but a lot of cpu cycles appear to be required in order to copy values from level to the next.

lori_m 

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.