Forum Discussion

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jan 25, 2024

A generalised Lambda helper function that return arrays of arrays using bisection.

  Specifically this required days of the week to be sorted by multiple price criteria. [This is intended as a topic for discussion as opposed to a specific request for help]   I used the problem ...
developer
excel
office 365
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jan 27, 2024

TheDub 

I think this does it!  I have removed the array of thunks from name manager and passed it as a parameter.

 

I have added an example that arose in a LinkedIn discussion from Marco Filocamo

(21) Post | Feed | LinkedIn

as well as your example that called for a similar text split.

The 'major' change for the user is to append the Greek letter λ to BYROW so that the formula picks up my function rather than the in-built helper function.

TheDub's avatar
TheDub
Iron Contributor
Jan 28, 2024
I still have to digest the "behind the scenes" a bit, but to paraphrase My Fair Lady: "By George! I think you've got it!"
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jan 29, 2024

    TheDub 

    I may well be running round in circles but I have learnt a lot from implementing the binary tree.  The feature that a really want to hold on to is that the syntax of the final formula (as well as the Lambda function) should match the in-built BYROW precisely.  I do have some concerns about computational efficiency though.

     

    I have written a BYCOL version that works will minimal modifications (basically changing ROWS(

    resultϑ) to COLS(resultϑ) and VSTACK to HSTACK.  I have confirmed that the existing BinaryTreeλ will also work with thunk arrays generated by SCAN, but the modified helper function SCANλ is harder to write.  The modification to the user-provided Lambda function not only has to convert the output to thunks but it also has to reverse the calculation when the first 'acc' parameter is read.

     

     

    • TheDub's avatar
      TheDub
      Iron Contributor
      Jan 29, 2024

      I may well be running round in circles

      I can only imagine what you brain must feel like in your skull...

      I have written a BYCOL version that works will minimal modifications

      Can you please share that too?

      I would argue that if you successfully covered BYROW and BYCOL, you have covered 70%-80% of the array-of-arrays cases (at least in my limited experience) and made a major contribution to our mental health and welfare.

      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Feb 05, 2024

        TheDub 

        Is the workbook unstable on your computer?  I think I will separate the SCAN, with the thunks used to handle the 2 column array used to collect the Fibonacci series, to another workbook since that is where the trouble started.   

         

        I have just submitted 'My Feedback' to Microsoft.  It is not a system I am familiar with, so we will see what happens!