Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
I want to return a non-contiguous subset of another array using the INDEX
function. For example: =INDEX({1,2,3;4,5,6;7,8,9},{1;3},{2,3}) When selecting a 2x2 array of cells and hitting CTRL + SHIFT + ENTER, the following array is returned: [2 3 8 9] However when using the formula "INDEX({1,2,3;4,5,6;7,8,9},{1;3},{2,3})" as an input to another function (within the actual formula) it seems only the top-left element is input i.e. (2). For example: ROWS(INDEX({1,2,3;4,5,6;7,8,9},{1;3},{2,3})) returns 1. As does, COLUMNS(INDEX({1,2,3;4,5,6;7,8,9},{1;3},{2,3})) Does anyone have any idea how to allow Excel to actually pass the entire array into the function rather than just the first element? |
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Isn't it just the function you are using, trying to get the row of a number
doesn't make sense. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Agenor" wrote in message ... I want to return a non-contiguous subset of another array using the INDEX function. For example: =INDEX({1,2,3;4,5,6;7,8,9},{1;3},{2,3}) When selecting a 2x2 array of cells and hitting CTRL + SHIFT + ENTER, the following array is returned: [2 3 8 9] However when using the formula "INDEX({1,2,3;4,5,6;7,8,9},{1;3},{2,3})" as an input to another function (within the actual formula) it seems only the top-left element is input i.e. (2). For example: ROWS(INDEX({1,2,3;4,5,6;7,8,9},{1;3},{2,3})) returns 1. As does, COLUMNS(INDEX({1,2,3;4,5,6;7,8,9},{1;3},{2,3})) Does anyone have any idea how to allow Excel to actually pass the entire array into the function rather than just the first element? |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Bob Phillips wrote...
Isn't it just the function you are using, trying to get the row of a number doesn't make sense. If the OP were calling ROW or COLUMN, your comment might apply. However the OP is calling ROWS and COLUMNS, functions which *both* operate on constant and derived arrays, so looks like your comment is irrelevant. "Agenor" wrote in message I want to return a non-contiguous subset of another array using the INDEX function. For example: =INDEX({1,2,3;4,5,6;7,8,9},{1;3},{2,3}) When selecting a 2x2 array of cells and hitting CTRL + SHIFT + ENTER, the following array is returned: [2 3 8 9] This is an example of Excel's implicit indexing. It's basically an undocumented 'feature'. When you pass arrays as 2nd or 3rd arguments to INDEX, it returns an object that works like a range reference when it comes to implicit indexing. That return object is something Excel can load into each selected cell as a separate result, but it's not something Excel can use as an expression in longer formulas. In the latter case, Excel will convert INDEX's return value into just the first item in the pseudoarray result. However when using the formula "INDEX({1,2,3;4,5,6;7,8,9},{1;3},{2,3})" as an input to another function (within the actual formula) it seems only the top-left element is input i.e. (2). For example: ROWS(INDEX({1,2,3;4,5,6;7,8,9},{1;3},{2,3})) returns 1. As does, COLUMNS(INDEX({1,2,3;4,5,6;7,8,9},{1;3},{2,3}) ) Does anyone have any idea how to allow Excel to actually pass the entire array into the function rather than just the first element? .... The only way to do this without VBA requires some basic linear algebra. For example, =MMULT({1,0,0;0,0,1},MMULT({1,2,3;4,5,6;7,8,9},{0, 0;1,0;0,1})) returns {2,3;8,9}, and the formula =ROWS(MMULT({1,0,0;0,0,1},MMULT({1,2,3;4,5,6;7,8,9 },{0,0;1,0;0,1}))) returns 2. If you want to generalize this, define the name A referring to ={1,2,3;4,5,6;7,8,9}, then try =MMULT(--(COLUMN($A$1:INDEX($1:$1,COLUMNS(A)))={1;3}), MMULT(A,--(ROW($A$1:INDEX($A:$A,ROWS(A)))={2,3}))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Using INDEX function to return array row. | Excel Worksheet Functions | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Formatting result of Index function | Excel Worksheet Functions | |||
SUMPRODUCT using and INDEX function doesn't total | Excel Worksheet Functions |