Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
LIVE
SOLVED

How to make an blank array?

Brass Contributor

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

yushang_0-1679027891499.png

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

12 Replies

@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.

 

@yushang 

Try this:

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

@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.

@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.

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

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

@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. 

@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. 

@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.

@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.

best response confirmed by yushang (Brass Contributor)
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)

 

Screenshot 2023-03-21 225106.png

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

BTW I found SORTBY(,) also works.

1 best response

Accepted Solutions
best response confirmed by yushang (Brass Contributor)
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)

 

Screenshot 2023-03-21 225106.png

View solution in original post