Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Strange behaviour when sorting rows | Excel Discussion (Misc queries) | |||
Unexpected (?) behaviour of OFFSET() in array formulas | Excel Discussion (Misc queries) | |||
Strange "FormatCells" behaviour | Excel Worksheet Functions | |||
Help, Urgent Excel Formulas are not calculating | Excel Discussion (Misc queries) | |||
Strange behaviour | Excel Worksheet Functions |