Forum Discussion

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 22, 2022

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! ...
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jan 23, 2022

SergeiBaklan 

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")
      )
    )
  )

 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 24, 2022

PeterBartholomew1 

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.

Resources