Forum Discussion
long texts with arrays in conditional functions
Let me illustrate by another simple but realistic example
In 5th row of the table is long text. I filter it and would like to return empty string instead of zero. It could be another logic, doesn't matter. The point is I'd like to keep all texts as they are.
Conditional function doesn't work in such situation, it doesn't return long text.
Lambda works, the simplest I found is MAP(). I may wrap all together with another lambda adding one more function as parameter for transformation.
But so far that's not for end user - no compatibility with majority of them due to lambdas. Even if they have they are not ready to maintain combination of lambdas.
At this point - I found nothing what works without lambdas and MAP() is simplest variant with lambdas.
Good or bad depends on goals, in this case the goal is to find easiest in maintenance and most compatible variant.
Finally, I get the point [I hope]. The solution I finished with was much the same as yours.
= MAP(
FILTER(Sample, Sample[id]<>3),
ReplaceNullsλ)
where 'ReplaceNullsλ' refers to
= LAMBDA(t,
IF(LEN(t)>0,t,"")
)
As for maintenance, I suspect the best option depends on the persons responsible for the task. I remember being told that the majority of spreadsheets in use do not contain any formulas [they do tend to have merged cells though]. The next batch contain interactively generated formulas such as
=H4+H7+H10+H13+H16+H19+H22+H25+H28+H31+H34+H37+H40+H43+H46+H49+H52+H55+H58+H61+H64+H67+H70+H73+H76+H79
because this is seen as 'simple'. The idea is that the end user can maintain the workbook and modify the formula as required with no real knowledge of Excel or more general coding environments.
I accept that the formulas, such as ours above, will never be typical and are likely to require some professional developer input for their maintenance. On the other hand, a small proportion of Excel user/developers still constitutes a pretty large community. The sort of problems you pose are not easy to solve, but my hope is that the use of the more rigorous programming techniques could ultimately halve the incidence of spreadsheet errors. Going from a 90% error rate to 45% would represent a huge advance [it sounds even more desirable if one expresses the change as moving from the current 10% of spreadsheets that are correctly formulated to 55%]. The challenge may be to convince users who currently develop solutions with VBA or DAX that there is something available within worksheet formulas that is worth returning to.
Thank you, your variant is much better, if only built lambda in
=MAP( FILTER( Sample, Sample[Id] <> 3 ), LAMBDA(v, IF( v = "", "", v ) ) )Will be perfect if do the same without lambdas, but it looks like it's impossible for now.
In general I agree with you comments. With some adjustment on my auditory. Significant part if it are professional software developers who use Excel quite eventually. They know concept of recursion, lambdas, functions like MAP() and use them every day in other environments. It'll be no problem to check help and understand specific of use in Excel. But they definitely won't work with ugly name manager or install not from the box utility which imitates kind of IDE.
In this situation simpler and more compact formulae the better.
I have some ideas on the last issue you mentioned of usability.
The formulas are now too large to be developed within a single cell, so that leaves the formula bar, which is itself too small and not well placed. The move to a side panel is probably correct, though there are situations in which a floating window on second screen would be better.
This should be used for all formulas with boxes rather like name manager determining whether the formula is to be loaded into the active cell, a sheet-local defined name or a global name. Lambda functions might also have data entry boxes for any test data used during formula development.
An interesting point might be 'what should be shown within the active cell whilst the formula is being developed in the side panel / floating window?' I would contend that it is pointless reproducing a fragment of the formula one is working on. What would be far more helpful is to show the numerical output (dynamic arrays) from steps that precede the current cursor position. This strategy would preserve the link between the cell and the formula that makes spreadsheet formulas part of functional programming. At the moment, debugging a faulty formula is something of a nightmare but, if one could simply step through the formula, that would change the user experience out of all recognition.
There may well be problems with nested formulas but the Evaluate Formula window offers a reasonable starting point, despite being totally unsuited to array calculation.