long texts with arrays in conditional functions

[Sergei Baklan]

@Peter Bartholomew , @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

[#2]

@Sergei Baklan 

=IF( {1}, REPT("a", 256) )

works for me if I confirm the formula with Ctrl+Shift+Enter (using Excel 2021, which has dynamic arrays).

[#3]

@Hans Vogelaar 

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,

[#4]

@Hans Vogelaar 

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?

[#5]

@Sergei Baklan 

I don't have LAMBDA functions, so let's see if someone else (Peter Bartholomew?) comes up with a better suggestion.

[#6]

@Hans Vogelaar , thank you. I still try to find some workaround without lambdas to keep compatibility, and one with lambda is not very elegant.

[#7]

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.

[#8]

so I was wondering if this affects IF vs alternatives and found similar results using IFS, CHOOSE, SWITCH. but, INDEX does seem to work. Can you rework the problem using the output of the IF be the input of the INDEX and the conditional of the IF be the indexing of the INDEX?

[#9]

@mtarler 

Yes, correct, you may add IFERROR() and IFNA() to the list.

With INDEX() perhaps you mean something like

=IF( INDEX( range, sequence ) , fn( <text>, sequence ) )

Nope, that doesn't work. As soon as we have an array instead of scalar value conditional functions refuse to work with long texts.

[#10]

@tboulden 

Thank you. Not sure about BYROW(), will play with it. MAKEARRAY() works, but formulas are so ugly...

[#11]

Agreed, I suspected MAKEARRAY would be viable also, but I agree with you that the formulae are ugly and unwieldy.

[#12]

@Sergei Baklan 

Sorry, I haven't got around to looking at the forum until now.  Try

= REDUCE("",SEQUENCE(512), 
    LAMBDA(concat,k, concat&"a")
  )

[#13]

or

= CONCAT(
     MAP(SEQUENCE(512),
         LAMBDA(k,"a")
     )
  )

[#14]

no i meant a more direct use of Index since adding the IF causes the issue. So in your simple IF example above: IF(A2:A3,B2#) doing something like INDEX(B2#,(A2:A3>0)*ROW(A2:A3)-1)

[#15]

@Sergei Baklan 

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

[#16]

@Peter Bartholomew 

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.

[#17]

@Sergei Baklan 

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.

[#18]

@Peter Bartholomew 

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.

[#19]

@Sergei Baklan 

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.

[#20]

@Peter Bartholomew 

As a cosmetic comment, any right pane could be placed main Excel window, you may consider right pane as floating window

The rest - yes, agree, hope at least some of that will be available soon.