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!
=IF( {1}, REPT("a", 255) ) works
SergeiBaklan
A simple workaround I came up with (maybe some others have, too):If({condition},Str1,Str2)(Str1, Str2 are arrays)
Yields errors for strings > 255 length. Alternative:
Left(Str1,Len(Str1)*({condition})) & Left(Str2,Len(Str2)*(1-{condition}))Similar concept can be adapted to other conditional situations.
Good idea, but not easy on practice. Especially if we use other conditional formulae, like
=CHOOSE( {7,2,5,3,9,1}, ....
Interesting -
I think the earlier idea of mtarler to use INDEX may work depending on context
A) IF({condition},str1,str2)
maybe one of:
=INDEX((str1,str2),,,1+{condition})
=INDEX(HSTACK(str1,str2),1+{condition})(first assumes cell references)
B) CHOOSE({7,2,5,3,9,1},col1,col2,...)
maybe instead:
=INDEX((col1,col2,...),SEQUENCE(ROWS(col1)),,{7,2,5,3,9,1})
=CHOOSECOLS(HSTACK(...),{7,2,5,3,9,1})(first assumes column ranges)
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.
Sorry, I haven't got around to looking at the forum until now. Try
= REDUCE("",SEQUENCE(512), LAMBDA(concat,k, concat&"a") )or
= CONCAT( MAP(SEQUENCE(512), LAMBDA(k,"a") ) )
=IF( {1}, REPT("a", 256) )works for me if I confirm the formula with Ctrl+Shift+Enter (using Excel 2021, which has dynamic arrays).
In general it works with MAP()
=LET( t, FILTER(range, criteria), MAP( t, LAMBDA(v, IF( v= "", "", v) ) ) )or back to simplest case
=LET( arr, {1}, MAP( arr, LAMBDA(v, IF( v, REPT("a", 256) ) ) ) )Perhaps shorter solution?
- Sergei, you honor me with thinking I can be of help š However, if I understand the problem correctly, I think you're on the right track with MAP, and I'd venture that MAP vs BYROW internal workings exemplify the issue between your examples using 1 vs {1}. Assuming single column array for arr, I think MAP takes the value from each row-cell, however BYROW takes each row-cell as an array.
Yes, it works. CSE returns first element of the array, other words works as with scalar value. In more complex case like
=LET( t, FILTER( range, criteria), IF( t = "", "", t ) )it doesn't work
Or, for example,
