User Profile
TheDub
Iron Contributor
Joined Aug 18, 2021
User Widgets
Recent Discussions
How - and when - does the accumulator of SCAN get reset?
This was inspired https://www.linkedin.com/posts/global-excel-summit_fill-blanks-with-value-from-above-activity-7279702477438955520-EysR?utm_source=share&utm_medium=member_desktop; I figure that this is a better forum to discuss the issue I would like to raise. In a nutshell, the assignment discussed there was to fill the blanks in a "gappy" column by repeating each visible value enough times to populate the empty cells beneath each visible value until the next visible value is reached and then continue repeating using that next value. The answer (as modified by PeterBartholomew1) in that discussion was: =SCAN("", names, LAMBDA(accumulator, current, IF(current <> "", current, accumulator))) In my attached file, "names" refers to the list of names in A1:A10 (that is, including the otherwise blank A10). A couple of things I don't understand: First (and probably simpler) is the function of the initial value of the accumulator. Whether it's "" or "xxx" (which I used in the attached file) the output stays the same. So why is the accumulator necessary to begin with? Or does it imply that - for that particular purpose - SCAN is not the right approach? Second, whether the initial accumulator value is "" or "xxx", it somehow gets reset as we go down the column and its value becomes equal to the value of the most recent visible row cell; it doesn't "accumulate" - which to my (obviously over-simplistic) mind would imply some sort of concatenation of all previously visible row cells. To demonstrate, I changed the formula to just =SCAN("xxx",names,LAMBDA(a,c,"a is now "&a)) and the output became In this case, there is some form of "accumulation" - but even that doesn't take the form I would expect. As the title says: how and when does the accumulator gets reset? That's an issue I ran into in various other scenarios so I would like to understand it once and for all, if possible...Another Issue with previous row/column references
This is a (simpler, I believe) variant of referring to PREVIOUS rows/columns issue but I still can't get my head around it: Given this: I want to insert in B2 a dynamic function which will show in each cell (B2:E2) the Row 1 value of the current column minus (or plus or whatever) the Row 2 value in the immediately preceding column, resulting, in this case, in 10 15 15 25 Using the bleeding edge version of 365.Re: Creating a Sliding Commission Scale in Excel
... there is an important point that modern Excel supports problem solving which is far closer to programming than it is to traditional spreadsheet authoring on the grid. Entirely agree - though I'm still now sure what this will do the future of Excel and its massive number of users. Will they change their approach to problem solving or just drop out? To solve the new problem, I first Curried the Commissionλ function Yes, tomorrow morning when my colleagues come back from their (US) Memorial Day weekend I'll try to explain this one to them and see where that takes meRe: A generalised Lambda helper function that return arrays of arrays using bisection.
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.Re: A generalised Lambda helper function that return arrays of arrays using bisection.
I must be dense (or my brain may have been scrambled by trying to wrap itself around the concept of thunks for so long) but let ask a simple question. You say: "it is intended to non-problem specific and hence reusable. ...the user provides a Lambda function that performs the required calculation as if BYROW would return the result.." So let's say I have range of three one-column rows each containing a first and last name, comma separated ("Elvis,Presley", for example). Trying =BYROW(range,LAMBDA(row,TEXTSPLIT(row,","))) results in your classic #CALC! and the admonition that "nested arrays are not supported". What do I need to modify in your attached workbook so as to get the desired output (a 2X3 array)? As you mention, this is not a specific request for help. In an ideal world (short of Excel resolving the array-of-arrays issue natively), I would have a black box into which I would feed the relevant array, the attack approach (by row, by column), the function to apply (TEXTSPLIT(), in this example) and the argument(s) required by that specific function (a delimiter, in this example) - and have the box spit out the correct outcome; something like: =blackbox(range,byrow,textsplit,",") One can only hope, right?
