2

Imagine I have several (i.e. > 100) column vectors of numbers. Vectors are large with equal length (e.g. 20k items). The vectors are not adjacent, so they don't make a matrix.

What I want, is to get some row-wise computation with the vectors, for instance

For each row what is the first non zero value among all vectors?

or

For each row what is the maximal value among all vectors?

See this simplified example, that should get the maximal value for all vectors, which would be 3 for all row (in reality the displayed value is 1):

enter image description here

It would be easy, if I could copy the vectors as a matrix and get the column of row ranges that spans all vectors for a given row, instead of the column ranges. But that is not the option due to the size of the data. I think it is related to other SO question: Is it possible to have array as an argument to INDIRECT(), so INDIRECT() returns array?.

Community
  • 1
  • 1
Adam Ryczkowski
  • 7,592
  • 13
  • 42
  • 68

2 Answers2

2

You can use CHOOSE to combine equal sized columns into a single range, e.g. for your 3 range example:

=CHOOSE({1,2,3},$B$1:$B$4,$B$5:$B$8,$A$3:$A$6)

Then use that directly in a formula, e.g. in G2 copied down to get the MAX in each row for your example

=MAX(INDEX(CHOOSE({1,2,3},$B$1:$B$4,$B$5:$B$8,$A$3:$A$6),F2,0))

or you can define the CHOOSE part as a named range [especially useful if you have 100 ranges], e.g. name that Matrix and use

=MAX(INDEX(Matrix,F2,0))

You need to modify the {1,2,3} part based on the number of ranges, to shortcut when you have 100 ranges you can use

=CHOOSE(TRANSPOSE(ROW(INDIRECT("1:100"))),Range1, Range2.....Range100)

Now needs to be confirmed with CTRL+SHIFT+ENTER

To get the first non-zero value you can use this version

=INDEX(INDEX(Matrix,F2,0),MATCH(TRUE,INDEX(Matrix,F2,0)<>0,0))

also confirmed with CTRL+SHIFT+ENTER

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • You are a real houdini to me! Its so fascinating and refreshing to find such bright minds on the SO! I've learned a lot today, thanks to you! – Adam Ryczkowski Nov 05 '13 at 17:23
  • I think I will start using two-dimensional array formulas on every worksheet I can. – Adam Ryczkowski Nov 05 '13 at 17:29
  • Is there any chance of having the idiom `CHOOSE({1,2,3},$B$1:$B$4,$B$5:$B$8,$A$3:$A$6)` to work with indirect ranges, like this: `CHOOSE({1,2,3},INDIRECT(Z1),INDIRECT(Z2),INDIRECT(Z3))`? It is strange, that the second form works well if pasted directly into worksheet, but fails to work when pasted as a named range... – Adam Ryczkowski Nov 06 '13 at 12:38
-1

I've found that you actually "can" return an array from INDIRECT().

However it must be in "R1C1" syntax AND you cannot create your R1C1 syntax with a formula (not with something like "R" & ROW() & "C" & COLUMN()".

You have to enter the ROW & COLUMN numbers as absolute and then it works.

Apparently excel puts {} around the numbers when they are returned by ROW() or COLUMN() function, and I guess that's why it doesn't work (try debugging, you'll see).

Alan Birtles
  • 32,622
  • 4
  • 31
  • 60
Wocky
  • 1
  • 1
    It wasn't me, who downvoted your answer, but I believe your post answers the question I stated in the comment section of the barry houdini's answer, so it should have been posted as an additional comment there, not as formal answer. Unless you have 1 point of reputation, and you cannot post comments. Which in this case as a catch-42 for you. SO is not a forum and is very serious with the policy of posts being strictly an answer to the question. – Adam Ryczkowski Sep 09 '18 at 16:47