Forum Discussion
why hstack change the cell type ?
Hi guys,
Take a look at the following figure, I fill C1 with formula HSTACK(SEQUENCE(9),A1:A9) , column B is used to check if column A is blanks and column E is used to check if column D is blanks.
I've never changed the format of column D, it is the default format and in my understanding, its values should be blanks.
My question is , why column B and E does not match? Many thanks!
- Internally and until passed to the worksheet, yes.
7 Replies
yushang That's not specific to HSTACK. In modern Excel a reference like =A1:A9 will spill all 9 cells in one instance, replacing empty cells by zeroes. That's by design, I believe.
If you want to avoid that behaviour use this in stead:
=IF(A1:A9="","",A1:A9)
In your case the formula would become:
=HSTACK(SEQUENCE(9),IF(A1:A9="","",A1:A9))
Or you could suppress the zeros by a custom number format, conditional formatting or by changing a workbook setting so that zeros are not displayed.
Riny_van_Eekelen But this is really strange , have look at following figure please. I fill B1 with formula
=LET(x,HSTACK(SEQUENCE(9),A1:A9),HSTACK(x,BYROW(x,LAMBDA(row,ISBLANK(INDEX(row,2))))))
D2 show that C2 is blank, but what C2 show is zero. So what's the real value of C2? Many thanks!
Why is it so strange? Internally, those are considered blanks, though when returned to actual worksheet cells they are by default rendered as numerical zeroes.
