CODEWITHSUNDEEP
arraysexcel-formulanamed
Derek
Derek

Reputation: 11

Find maximum value in each row or column of an Excel array

In Excel I have a named range that contains an array, for example like the array below.

0 0 0 7
0 0 6 5
0 5 3 2
4 3 2 1

I'd like to find the maximum value in each row (or column) of the array. For the columns in the array above I want the result to be:

Array={4,5,6,7}

If it helps, the maximum is always going to be the topmost number for a column and leftmost number for a row.

I would like a worksheet formula rather than a VBA function.

Upvotes: 1

Views: 5934

Answers (3)

Stephen Hunger
Stephen Hunger

Reputation: 1

please note that

=MAX(INDEX(rng, ROW(A1:A4))) and =MAX(INDEX(rng, ROW(A1:A4)))

only work if your array starts in A1 as it is relative to the array not the position in the sheet

Upvotes: 0

Dave Wenta aka FlameRetired
Dave Wenta aka FlameRetired

Reputation: 11

With a named range Rng and values as posted in original post try this formula.

=SUBTOTAL(4,OFFSET(INDEX(Rng,1,1),,COLUMN(Rng)-MIN(COLUMN(Rng)),ROWS(Rng)))

It returns an array of max of each column {4,5,6,7}

This returns a max of each row. 

=SUBTOTAL(4,OFFSET(INDEX(Rng,1,1),ROW(Rng)-MIN(ROW(Rng)),,,COLUMNS(Rng)))

and an array {7;6;5;4}.

Upvotes: 1

Derek
Derek

Reputation: 11

I have found a solution. If my initial array is named rng then the following array formula can be used to return an array showing the maximum number in each column:

=MAX(INDEX(rng,0,COLUMN(A1:D1)))

Similarly, to return the maximum in each row:

=MAX(INDEX(rng, ROW(A1:A4)))

Upvotes: 0

Related Questions