Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Returning an array from the INDEX function

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,726
Default Returning an array from the INDEX function

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 733
Default Returning an array from the INDEX function

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using INDEX function to return array row. RBI Excel Worksheet Functions 1 October 4th 06 04:21 PM
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) [email protected] Excel Worksheet Functions 0 September 5th 06 04:44 PM
Custom functions calculating time arguments Help Desperate Bill_De Excel Worksheet Functions 12 April 25th 06 03:22 AM
Formatting result of Index function ExcelFred Excel Worksheet Functions 5 July 26th 05 02:34 PM
SUMPRODUCT using and INDEX function doesn't total rlutes Excel Worksheet Functions 8 June 5th 05 01:29 AM


All times are GMT +1. The time now is 02:12 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"