How to make an blank array?

[yushang]

Hi guys,

I've tried with the following formula to make a blank array but in vain 

=LET(x,MAKEARRAY(2,2,LAMBDA(r,c,"")),ISBLANK(x))

So what should I return in the LAMBDA to make the x a blank array? Many thanks!

[#2]

@yushang An empty string like "" does not equal a blank. That why you get FALSE. Turn it into this:

=LET(x,MAKEARRAY(2,2,LAMBDA(r,c,"")),x="") to get all TRUE.

[#3]

@yushang 

Try this:

=EXPAND("",2,2,"")

[#4]

@yushang 

Formulae in Excel, includes lambdas, do not return into the grid blank as a value. You may return zero by default (formatted as FALSE in your case), or empty string "", or any other value. Not blank.

[#5]

@yushang 

Within the LET formula try replacing the double quotes with IF(,,) or SORT(,) to return an array of TRUE values. For this purpose, it may be helpful to define names:

missing:=IF(,,)

empty:=@SORT(,)

I find nulls particularly confusing in Excel. Although they are displayed as zeroes in formula results, depending on context they may also result in "" or FALSE.  Formula debugging really needs to be improved in my view.

[#6]

@Patrick2788 They are not the true blanks. ISBLANK(EXPAND("",2,2,"")) return {false,false;false,false}

[#7]

What's the goal in returning 'true blanks'?

[#8]

@Patrick2788 I just want to use ISBLANK. I found 2 solutions

1. reference a blank cell, for example, A65536, which is far enough from occasionally modify.

2. do not use ISBLANK, instead, use NA() and ISNA() pair, this solution is more robust. 

[#9]

@Patrick2788

I had tried this same exercise to gain a better understanding of the formula language for building lambdas, since one often needs to account for null inputs. ISBLANK can be contrasted with ISOMITTED which returns a single FALSE value when given an array of blanks but does returns TRUE for the missing definition above. 

[#10]

@lori_m 

From that point of view slightly modified initial formula

=LET(x, MAKEARRAY(2,2, LAMBDA(r,c, LAMBDA([n],n)() ) ), ISBLANK(x) )

returns all TRUE.

Still didn't catch what is the purpose.

[#11]

@Sergei Baklan 

Yes, I guess there are many variations, another for array of empty/missing values:
=LET(x,EXPAND(SORT(,),2,2,),ISBLANK(x))

Wrting unit tests for lambdas was the purpose for me as well as background knowledge. I think nulls can also be useful in data transformations to distinguish from other values like "" and NA() - the trouble is available formula tools make them difficult to work with.

[#12 · Accepted solution]

Below is an example of a data transformation using blank values within a pivoted array.

Note for display in the grid null strings are used:

MATRIX(row,col,val,"")

but for arithmetic calculations, blank values are used instead::

MATRIX(row,col,val)

[#13]

@lori_m Thanks. SORT(,) is the point which return true blank!

BTW I found SORTBY(,) also works.