Forum Discussion
How to make an blank array?
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)
Patrick2788 They are not the true blanks. ISBLANK(EXPAND("",2,2,"")) return {false,false;false,false}
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.
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.
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.
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.
