Forum Discussion
long texts with arrays in conditional functions
PeterBartholomew1 , tboulden or someone else
Perhaps you know the workaround, even with lambdas. In simplest case
=IF( 1, REPT("a", 256) ) works
=IF( {1}, REPT("a", 256) ) returns #VALUE!
...
I think I got the wrong end of the stick. Your challenge was to make the array IF work with long strings, not creating the strings themselves?
The following formula produces alternating strings of "a"s and "b"s but I am still not sure whether it is relevant to the problem.
= BYROW(ISODD(SEQUENCE(5))+SEQUENCE(1,512,0,0),
LAMBDA(r_,
CONCAT(
IF(r_,"a","b")
)
)
)
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+H79because 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.
