Array of calculated values

[kazreh]

Hi,

Is it possible to have an array in a formula made up of calculated values?

For example something like this (does not work):
=SUMPRODUCT({1;2};{SUMPRODUCT(A1:A10; B1:B10); SUMPRODUCT(B1:B10; C1:C10)})
=SUMPRODUCT({fixed array};{calculated array})

If not directly, is there a workaround?

 

[FormR]

Hi, if this is your real world example example then it could be written like as:

=SUM(SUMPRODUCT(A1:A10;B1:B10);SUMPRODUCT(B1:B10;C1:C10)*2)

Or to turn the two seperate results of the inner SUMPRODUCT formula to an array you could employ something like..

=SUMPRODUCT({1;2};CHOOSE({1;2};SUMPRODUCT(A1:A10;B1:B10);SUMPRODUCT(B1:B10;C1:C10)))

But there might be better ways depending on what your final goal is.

 

[kazreh]

Thanks once again FormR! I now know that there is a CHOOSE function.
Thanks to your solution, combined with your solution from my last question, now the problem is solved.

I'm sure there is a better way to do what I have wanted to acheive but a working solution is good enough for me.