Suppose I have A = {1;2;3;4;5}
I want to replace i-th element to value x. If i=3, x=9, then B = {1;2;9;4;5}
Now I make custom function to do this assignment: =LAMBDA(array,i,val,VSTACK(INDEX(array,SEQUENCE(i-1)),val,INDEX(array,SEQUENCE(ROWS(array)-i,,i+1))))
But when I use it with ARRAYTOTEXT function, it gives incorrect result: C=ARRAYTOTEXT(Assign(A,3,9),1)={1;9;4}
It seems to have something to do with VSTACK that ARRAYTOTEXT recognize only VSTACK output's first value.
How do I solve this? Is there other way to do value assignment to an array that compatible with arraytotext function? Thanks
I want to replace i-th element to value x. If i=3, x=9, then B = {1;2;9;4;5}
Now I make custom function to do this assignment: =LAMBDA(array,i,val,VSTACK(INDEX(array,SEQUENCE(i-1)),val,INDEX(array,SEQUENCE(ROWS(array)-i,,i+1))))
But when I use it with ARRAYTOTEXT function, it gives incorrect result: C=ARRAYTOTEXT(Assign(A,3,9),1)={1;9;4}
It seems to have something to do with VSTACK that ARRAYTOTEXT recognize only VSTACK output's first value.
How do I solve this? Is there other way to do value assignment to an array that compatible with arraytotext function? Thanks