0

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.

Derek
  • 1
  • 1
  • 3
  • Do you happen to mean worksheet formula or VBA function? Your question implies VBA but the tags imply ws. – user3819867 Apr 21 '15 at 08:39
  • I assume you don't want a worksheet formula to literally output "Array={4,5,6,7}"? What is the context for wanting to do this? – Steven Apr 21 '15 at 11:55
  • @Steven No, I don't literally want "Array=" etc. I want an array with which I can do further calculations. I want that new array to only include the maximum values from each row or column of the original array. – Derek Apr 21 '15 at 21:21

3 Answers3

1

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}.

Litisqe Kumar
  • 2,512
  • 4
  • 26
  • 40
Dave Wenta aka FlameRetired
  • 11
  • 2
0

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)))
Derek
  • 1
  • 1
  • 3
0

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

SwissCodeMen
  • 4,222
  • 8
  • 24
  • 34
Stephen Hunger
  • 1