Forum Discussion
Wider Excel discussion
My biggest pet peeve is that DROP-REDUCE-VSTACK and MAKEARRAY-INDEX are still the most commonly suggested dynamic array methods found on any Excel forum. I too was initially drawn to them when I made the switch to Office 365, as the logic is very similar to a For Each / For Next loop in VBA. Fortunately, David Leal was kind enough to advise me very early on about their efficiency issues, and I've been actively seeking out and sharing alternative methods ever since. It feels like a losing battle, though, as everywhere I turn it's DROP-REDUCE-VSTACK and MAKEARRAY-INDEX over and over again...
Not sure if that's what you meant by "low-grade material", but they're definitely "low-grade" in my book. Your custom MAPλ and EVALTHUNKARRλ functions are still the best generic alternatives I've come across to date. ;)
Hi David,
Craig Hatmaker is including some thunks in his BXL 5g modules.
I am getting comfortable with using thunks to reference arrays that I want to access within a calculation. If I am not allowed an array of ranges/arrays, sobeit; I will use an array of functions. What I would like some support on is getting Microsoft to implement a built-in function that evaluates and stacks an array of thunks as EVALTHUNKARRλ does. Our function is full of tricks and works pretty well but I can't help thinking that it would perform better and be more acceptable to the user community as a built-in function.
On an entirely different track, something else I would like is
= VSTACK( STYLE(result, "Output"), STYLE(BYCOL(result, SUM), "Total") )which could give nicely formatted output without resorting to the nightmare world of conditional formatting!
I like the idea of a robust STYLE function to replace legacy conditional formatting. A client had asked me the other day if I thought conditional formatting would be updated any time soon. I suspect most legacy features that open with the old dialogue menu are likely going to stay the same with improvements added as new features altogether - such as a STYLE function.
Granted, I think such a function would likely only be used by a developer. I'm finding most functions introduced since the advent of dynamic arrays are not on most organizations' radars. This is good in a sense that one does not usually stumble upon Lambda, for example, similar to how one could find macros. Much less things to break! Though I believe Excel is still very much underestimated in what it's capable of doing.
I suspect that the Styles functionality within Excel is grossly underused. The actual default values chosen by Microsoft are ridiculous but the basis for a better structured use of formatting is there. To be able to invoke the formatting by formula as an alternative to the GUI would open up new possibilities for the 'one formula sheet'.
Agreed: a built-in EVALTHUNKARRλ function would be ideal. The STYLE concept would also be neat, if they could pull it off. Perhaps something akin to CSS, where you define and apply your own custom styles?
Sounds like Pivot Tables are getting an "Auto-Refresh" option, which is long overdue. GROUPBY and PIVOTBY are great, but difficult to format (they also lack basic options like choosing to "insert blank line after each item label" or to NOT "repeat item labels"). I tend to use them more as an intermediary step for solving larger problems, rather than the final output. It would be super helpful if Conditional Formatting rules could be applied to a dynamic spill range. 😏
