Forum Discussion

Brass Contributor

Mar 17, 2023

Solved

How to make an blank array?

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

excel

lori_m
Mar 21, 2023
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)

SergeiBaklan

Diamond Contributor

Mar 19, 2023

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.

lori_m

Iron Contributor

Mar 19, 2023

SergeiBaklan

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.

lori_m
Iron Contributor
Mar 21, 2023
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)
- yushang
  Brass Contributor
  Mar 22, 2023
  lori_m Thanks. SORT(,) is the point which return true blank!
  BTW I found SORTBY(,) also works.