Create one-dimensional array from single cell (repeating that cells value n times)

[kderoeck]

Hi,

I'm looking for an excel formula that can create a one-dimensional array from a single cell, repeating the cells value n times. e.g. cell A1 has value 200, the result of the formula should be an array that looks like this: {200,200,200,200,200,200,200}

The challenge lies in the fact that I would like to avoid using VBA as I'm pretty sure the same result can be achieved through excel formulas.

Kind regards,
Koen

 

[Andrew Poulsom]

This returns an array when examined in the Evaluate Formula dialog.

=INDEX((A1*ROW(1:7))/ROW(1:7),)

 

[Peter_SSs]

What are you going to use the array for? That might open up some other options.

 

[kderoeck]

Hi Andrew,

Great! Thank you very much! This formula gives me the result I was looking for.

Any idea why I cannot use your formula in conjucntion with a sumproduct?

e.g. =SUMPRODUCT(INDEX((A1*ROW(1:7))/ROW(1:7),),C2:G2)

The underlying idea is that I want to apply seasonality (range C2:G2) onto a flat forecast (A1)

Kind regards,
Koen

 

[Andrew Poulsom]

There are only 5 columns in C:G. But you don't need to got to those lengths. If you multiply within SUMPRODUCT it will be expanded automatically:

=SUMPRODUCT(C2:G2*A1)

 

[kderoeck]

Hi Andrew,

Life can be so easy sometimes ;o)
Thanks for the excellent support!

Kind regards,
Koen

 

[akshaythakker]

hi Andrew:

I understand this is an old post but I thought I might give it a shot at asking it in this context instead of creating a new thread. 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

 

[Kipperfer]

Akshay
Now, with dynamic array formula ...
=IF(MOD(SEQUENCE(A1,A1)-1,A1)+INT(SEQUENCE(A1,A1,0)/A1)>=A1,0,INT(SEQUENCE(A1,A1,0)/A1)+1)
where a1 is the column number

 

[akshaythakker]

Akshay
Now, with dynamic array formula ...
=IF(MOD(SEQUENCE(A1,A1)-1,A1)+INT(SEQUENCE(A1,A1,0)/A1)>=A1,0,INT(SEQUENCE(A1,A1,0)/A1)+1)
where a1 is the column number

Thanks Kipperfer, can this be done without a sequence command? the desktop version of excel dont seem to have this command!

 

[Peter_SSs]

the desktop version of excel dont seem to have this command!

It depends on which desktop version you have. Therefore ..

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
eg