Creating a dynamic formula to create an array constant

[akshaythakker]

Hello All:

I am looking for a way to create a dynamic array constant which has the value of {1,1,1;2,2,0;3,0,0} in column 3 and {1,1,1,1;2,2,2,0;3,3,0,0;4,0,0,0} in column 4.. and so on and so forth

This is where I have reached so far:
I was able to figure out that an array formula =CHOOSE(TRANSPOSE(A1:C1),{1,1,1},{2,2,0},{3,0,0}) where A1=1,B1=2,C1=3 gives me the solution and =A1*--(A1:A3<D1) gives me the value of {1,1,1}.. but when I try combining the above two into a single forumla as =CHOOSE(TRANSPOSE(A1:C1),A1:C1*--(OFFSET(A1:C1,0,0,1,C1)<D1)).. i am returned the value of {1,2,3;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!}....

I cant seem to figure out how to get this to work or some other way to get the constant {1,1,1;2,2,0;3,0,0}.

Please do help.

Thanks
Akshay

 

[akshaythakker]

for some reason a par of my post got deleted. here is the paragraph again

This is where I have reached so far:

<d1) gives="" me="" the="" value="" of="" {1,1,1}..="" but="" when="" i="" try="" combining="" above="" two="" into="" a="" single="" forumla="" as="CHOOSE(TRANSPOSE(A1:C1),A1:C1*--(OFFSET(A1:C1,0,0,1,C1)<D1)).." am="" returned="" {1,2,3;#value!,#value!,#value!;#value!,#value!,#value!}....<="" html=""></d1)>I was able to figure out that an array formula

=CHOOSE(TRANSPOSE(A1:C1),{1,1,1},{2,2,0},{3,0,0})

where A1=1,B1=2,C1=3 gives me the solution and

=A1*--(A1:A3<D1)

gives me the value of {1,1,1}.. but when I try combining the above two into a single forumla as

=CHOOSE(TRANSPOSE(A1:C1),A1:C1*--(OFFSET(A1:C1,0,0,1,C1)<D1))

.. i am returned the value of {1,2,3;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!}....
<d1)[ code]="" gives="" me="" the="" value="" of="" {1,1,1}..="" but="" when="" i="" try="" combining="" above="" two="" into="" a="" single="" forumla="" as=""

="CHOOSE(TRANSPOSE(A1:C1),A1:C1*--(OFFSET(A1:C1,0,0,1,C1)<D1))

.." am="" returned="" {1,2,3;#value!,#value!,#value!;#value!,#value!,#value!}....
<d1) gives="" me="" the="" value="" of="" {1,1,1}..="" but="" when="" i="" try="" combining="" above="" two="" into="" a="" single="" formula="" as="CHOOSE(TRANSPOSE(A1:C1),A1:C1*--(OFFSET(A1:C1,0,0,1,C1)<D1)).." am="" returned="" {1,2,3;#value!,#value!,#value!;#value!,#value!,#value!}....<="" html=""></d1)></d1)[>