CODEWITHSUNDEEP
excelexcel-formula
Adam Ryczkowski
Adam Ryczkowski

Reputation: 8064

Why this array formula doesn't work?

enter image description here

On the illustration all formulas are array. The range that each formula spans is bordered, and the first formula on each block is written on the top of that block.

Range A4:A103 is an input vector (which is numeric), range C4:G23 is a given (input) permutation of the rows of A4:A103 (necessarily positive non-zero integer numbers not greater then the length of the input vector).

Let us I interpret the permutation matrix as set of rows.

How to compute for each row in a constant number of cells the minimal number in the input vector? By the constant number of cells, I mean solution, that would require fixed number of cells for each row, regardless of the number of columns in permutation. (In the production case each dimension is much, much bigger; there is about 100 columns in the permutation matrix.)

I don't ask for VBA solutions. If it is necessary the solution can use a free and publicly available Excel add-on, like MoreFunc, but I'd prefer to keep it vanilla Excel 2007 or later.

I thought that the formula {=MIN(INDEX(INDIRECT($A$2);$C4:$G4))} would solve my problem. Surprisingly, Excel seems to not take into account the array nature of the formula, and evaluates it as if it was written as =MIN(INDEX(INDIRECT($A$2);$C4) which is equivalent to dysfunctional =INDEX(INDIRECT($A$2);$C4).

On the other hand, we can see the argument to the MIN is understood as array in the range I4:M4.

Upvotes: 1

Views: 3405

Answers (1)

barry houdini
barry houdini

Reputation: 46331

INDEX works in some strange ways!

Normally INDEX can't return an array - although you seem to have found the one exception to that - when it's an array formula entered in a range.

You should be able to use OFFSET to return the required array that will work within MIN, i.e. with this formula

=MIN(N(OFFSET(INDIRECT($A$2);$C4:$G4-1;0)))

confirmed with CTRL+SHIFT+ENTER

Upvotes: 3

Related Questions