Forging a peculiar array

**kayard** · 06-23-2010, 08:21 AM

Hi,

does anyone know how to construct an array of 4 elements, 3 of which are the content of 3 cells (say a1:c1) and the 4th element is simply 1 ? The difficult part is that the array must be created inside a bigger formula which make use of the array. It cannot be created by linking to 4 cells on the sheet where 3 of them are linked to a1:c1 and the fourth is 1...

I can try to explain with a syntax that unfortunately doesn't work

=SUMPRODUCT(array1, {a1:c1;1})

thanks for any support

Paolo

Italy

**daddylonglegs** · 06-23-2010, 08:36 AM

You could use CHOOSE like this

CHOOSE({1,2,3,4},A1,B1,C1,1)

Of course that will only be an option for relatively small arrays

**kayard** · 06-23-2010, 08:41 AM

Thanks but that won't help me for the array is variable in lenght (dynamic array) in the sense that the formula is copied along a row and for each new collumn the array is bigger by one cell.

Any other idea ?

**DonkeyOte** · 06-23-2010, 08:54 AM

Use in conjunction with INDEX

INDEX(CHOOSE({1,2,3,4},A1,B1,C1,1),0)

**kayard** · 06-23-2010, 09:07 AM

I don't understand how it works but it does (i'll think about it) ...

Still it's not enought because you have to explicitly reference cell a1;b1;c1 and this is a problem cause in my sheet the formula is used 30 times, the 1st in collumn D and it references a1:b1:c1 BUT in collumn z it would have to reference a1;b1;c1;d1 .... w1 and the first array would have to be {1,2,3,4 ...20}.

In other words the formula is not "scalable". I cannot symply copy it accross the row ...

Sorry for my english

Thanks

**DonkeyOte** · 06-23-2010, 09:24 AM

It would, I suspect, be a great deal wiser to outline what it is you're calculating and why ... whether or not you need to do the above is likely to be open to debate.

A sample file would of course be the best course of action at this point.

(I, like most other Englishmen, will be off line for the next few hours but others will be around to assist you)