Forum Discussion
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 array? Many thanks!
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)
12 Replies
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.
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.
Patrick2788 They are not the true blanks. ISBLANK(EXPAND("",2,2,"")) return {false,false;false,false}
- What's the goal in returning 'true blanks'?
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.
