strange behaviour with matrix formulas
 

Hi,

I'm slightly puzzled.

Can anybody explain me why the following formula works as expected
(returns 320) :

=SUMPRODUCT(INDEX(Sheet2!B2:D6;C6;0);INDEX(sheet3! B2:D6;C6;0))

BUT, if I put in cell H1 (for example)

INDEX(Sheet2!B2:D6;C6;0) and

INDEX(sheet3!B2:D6;C6;0) in H2

and then

SUMPRODUCT( H1;2) this doesn't work as expected ?

NB: I paid attention to enter formula in H1 and H2 as matrix (CTRL +
SHIFT + ENTER) ...

Any guess ?


--
kayard
------------------------------------------------------------------------
kayard's Profile: http://www.excelforum.com/member.php...o&userid=24716
View this thread: http://www.excelforum.com/showthread...hreadid=542448

strange behaviour with matrix formulas
 

From my understanding, when you enter a formula that returns an array in a
single cell, it will return the first element of the array. The formula
SUMPRODUCT expects arrays, and in the first case the INDEX formulae are
giving two arrays if the formula is enter as an array formula, while the
second case it is just getting two scalars from the cells H1 and H2.

Hope this helps,
Miguel.

"kayard" wrote:


Hi,

I'm slightly puzzled.

Can anybody explain me why the following formula works as expected
(returns 320) :

=SUMPRODUCT(INDEX(Sheet2!B2:D6;C6;0);INDEX(sheet3! B2:D6;C6;0))

BUT, if I put in cell H1 (for example)

INDEX(Sheet2!B2:D6;C6;0) and

INDEX(sheet3!B2:D6;C6;0) in H2

and then

SUMPRODUCT( H1;2) this doesn't work as expected ?

NB: I paid attention to enter formula in H1 and H2 as matrix (CTRL +
SHIFT + ENTER) ...

Any guess ?


--
kayard
------------------------------------------------------------------------
kayard's Profile: http://www.excelforum.com/member.php...o&userid=24716
View this thread: http://www.excelforum.com/showthread...hreadid=542448

strange behaviour with matrix formulas
 

Yes but I entered the formulas in h1 and h2 as array formulas so there
shouldn't be any difference..


--
kayard
------------------------------------------------------------------------
kayard's Profile: http://www.excelforum.com/member.php...o&userid=24716
View this thread: http://www.excelforum.com/showthread...hreadid=542448

strange behaviour with matrix formulas
 

The difference is that a cell cannot contain an array, just an element of the
array. If you enter a formula like
={1,2,3}
in a cell, even if you enter it as an array formula, it will show the
element 1, and behave as the element 1 (scalar), not as the whole array.

Miguel.

"kayard" wrote:


Yes but I entered the formulas in h1 and h2 as array formulas so there
shouldn't be any difference..


--
kayard
------------------------------------------------------------------------
kayard's Profile: http://www.excelforum.com/member.php...o&userid=24716
View this thread: http://www.excelforum.com/showthread...hreadid=542448