Hi,
I am looking for a formula to produce a vertical array containing the maximum values from each row in a 2D matrix.
As an example, consider the following formula in cell A1, producting a 2D matrix:
The vertical array containing the maximum from each row in the 2D matrix would look something like this:
The following formula works if it references the matrix formula's spill range with A1#:
But if the A1# references are replaced with the matrix formula itself, it will not work because of the INDEX(ROW(A1:A3)*COLUMN(A1:C1),1,1) part:
I would like to have a formula that is "self containing", meaning containing the formula producing the 2D matrix and which doesn't contain volatile functions like OFFSET. Any ideas?
Best regards,
Marbleking
Bookmarks