Hi,
I'm experimenting with some stuff in Excel (because I'm a geek, mainly) and I came across a problem without solution. I expect it doesn't have a solution, but that's fine.
It's specific to those with access to dynamic array formulas only (probably, I think...). I can write the word "Test" in a cell, and in the adjacent cell type =TRANSPOSE(SEQUENCE(3)), which gives me the following:
What I can't do is get the same returned when using =CHOOSE({1,2},"Test",TRANSPOSE(SEQUENCE(3)))
It seems that the CHOOSE formula cannot be nested (or used in combination with sequence or other array) to return more columns than the initial choose statement's proposal. Even changing the formula to CHOOSE({1,2,3,4,5}... has no bearing. Interestingly, the number returned in the first column is the second value in the array too, so if I subtract 1 then the first column value is the correct value, but subsequent columns are not filled out. If I change the formula to:
=CHOOSE({1,2,3,4},"Test",(TRANSPOSE(SEQUENCE(4,1,0,1))))
I get the correct number of columns returned, but the last two columns get the #VALUE! error.
Anyway, I'm guessing there is no way to "nest" the CHOOSE function in this manner. If anyone finds a solution, that'd be cool. If not, it's really just something I was pondering anyway!
I'm experimenting with some stuff in Excel (because I'm a geek, mainly) and I came across a problem without solution. I expect it doesn't have a solution, but that's fine.
It's specific to those with access to dynamic array formulas only (probably, I think...). I can write the word "Test" in a cell, and in the adjacent cell type =TRANSPOSE(SEQUENCE(3)), which gives me the following:
Test | 1 | 2 | 3 |
What I can't do is get the same returned when using =CHOOSE({1,2},"Test",TRANSPOSE(SEQUENCE(3)))
It seems that the CHOOSE formula cannot be nested (or used in combination with sequence or other array) to return more columns than the initial choose statement's proposal. Even changing the formula to CHOOSE({1,2,3,4,5}... has no bearing. Interestingly, the number returned in the first column is the second value in the array too, so if I subtract 1 then the first column value is the correct value, but subsequent columns are not filled out. If I change the formula to:
=CHOOSE({1,2,3,4},"Test",(TRANSPOSE(SEQUENCE(4,1,0,1))))
I get the correct number of columns returned, but the last two columns get the #VALUE! error.
Anyway, I'm guessing there is no way to "nest" the CHOOSE function in this manner. If anyone finds a solution, that'd be cool. If not, it's really just something I was pondering anyway!