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)

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.