Finding Location of Maximum Value in 2D Array

  • Thread starter Thread starter MajorSetback
  • Start date Start date
M

MajorSetback

I am using MS Office ExCel 2003(11.6560.6568) SP2 and have a single
sheet with a 2D array of numbers roughly 202x202 in size. Could
someone tell me the quickest way to locate the maximum value in the
array? I tried =WhereMax("Sheet1") but just got #NAME? as the result.

Any assistance would be greatly appreciated,
Peter.
 
Hi Major:

Sub findmax()
Dim r As Range
Set r = Selection
v = r.Cells(1, 1).Value
For Each r In Selection
If r.Value > v Then
v = r.Value
s = r.Address
End If
Next
MsgBox ("maximum value " & v & " found in cell " & s)
End Sub

This can easily be converted into a function as well.
 
You could add a row that contains the formula =max(a1:a202) with the
result that the row contains the maximum of each column. Then create a
new column the has a simular formula getting the max of each row.
Finally, use the
=match(max([row_of_max_column_values],[row_of_max_column_values],0) to
find the column that contains the maximum number. Then do another
match that looks through your new 'max of rows' column to get the row
number.

Hope this helps,
Horst
 
If your data is in B1:C202 then...
In A1 =MAX(B1:C202)
In E1 =IF(ISERROR(MATCH(A1,B1:B202,FALSE)),ADDRESS(MATCH(A1,C1:C202,FALSE),3),ADDRESS(MATCH(A1,B1:B202,FALSE),2))

Gives you the cell location of the maximum value.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



<[email protected]>
wrote in message
I am using MS Office ExCel 2003(11.6560.6568) SP2 and have a single
sheet with a 2D array of numbers roughly 202x202 in size. Could
someone tell me the quickest way to locate the maximum value in the
array? I tried =WhereMax("Sheet1") but just got #NAME? as the result.

Any assistance would be greatly appreciated,
Peter.
 
Gary''s Student wrote...
Sub findmax()
Click to expand...

If there were OBVIOUSLY something for which a FUNCTION would be most
appropriate, this is it!
Dim r As Range
Set r = Selection
v = r.Cells(1, 1).Value
For Each r In Selection
If r.Value > v Then
v = r.Value
s = r.Address
End If
Next
MsgBox ("maximum value " & v & " found in cell " & s)
End Sub

This can easily be converted into a function as well.
Click to expand...

Should NEVER have been offerred as a sub. Parametrizing the function
isn't completely trivial, and deciding what to return (range object
pointing to the cell containing the max value, a string containing the
cell's address or an array of row and column indices) is worthy of some
thought.

Also inefficient. No reason to search through the entire range.
WorksheetFunction.Max will return the max value more quickly than
iterating through the range in VBA, then exit when the first instance
of the max value is found. The following returns the address of that
cell.

Function foo(rng As Range) As String
Dim c As Range, x As Double
x = Application.WorksheetFunction.Max(rng)
For Each c In rng
If c = x Then
foo = c.Address(0, 0)
Exit Function
End If
Next c
End Function

There's also the question of which direction to search. That is, if the
range contained

1 1 1 9 1
2 1 3 1 1
3 1 2 2 3
9 3 1 1 2
2 1 1 3 2

should the 9 in the top row or the 9 in the leftmost column be
considered the first max value found?

Finally, no need for udfs. This can be done with an array formula. If
the range were named D,

=ADDRESS(INT(MIN(IF(D=MAX(D),ROW(D)*1000+COLUMN(D)))/1000),
MOD(MIN(IF(D=MAX(D),ROW(D)*1000+COLUMN(D))),1000))

would return the address of topmost then leftmost max value and

=ADDRESS(MOD(MIN(IF(D=MAX(D),ROW(D)+1000*COLUMN(D))),1000),
INT(MIN(IF(D=MAX(D),ROW(D)+1000*COLUMN(D)))/1000))

would return the address of the leftmost then topmost max value.
 
Let's say that your 202x202 array of numbers is A2:GT203. In cell A1 type
the following to give you the maximum value in the array:

=max(a2:gt203)

but instead of pressing Enter, hold down the Shift & Control keys and press
Enter. This creates an array formula.

If you want to find what cell it's in, you can highlight the array and use
Edit-Find and enter the max value from A1.
 
Hi Harlan:

Your suggestion to use MAX and then loop for it is very good. I tried the
following:

Sub findmax()
Dim s As String
m = Application.Max(Selection)
s = Selection.Find(What:=m, After:=ActiveCell).Address
MsgBox (s)
End Sub

That is using Find in place of a loop. This works. I then tried to make a
function:

Function superfoo(r As Range) As String
Dim s As String
m = Application.Max(r)
superfoo = r.Find(What:=m, After:=ActiveCell).Address
End Function

This throws a #VALUE! error.

What am I doing wrong?
 
Gary''s Student wrote...
....
Function superfoo(r As Range) As String
Dim s As String
m = Application.Max(r)
superfoo = r.Find(What:=m, After:=ActiveCell).Address
End Function

This throws a #VALUE! error.

What am I doing wrong?
Click to expand...
....

Find is a *method* rather than a property of the Range class. You can't
run methods from udfs because udfs can't do anything other than return
values to the calling cell. The Find method tries to change ActiveCell,
but udfs can't do that.
 
This seems to work...
Function superfoo2(r As Range) As String
Dim m As Double
m = Application.Max(r)
superfoo2 = r.Find(What:=m, after:=r(r.Count)).Address
End Function
------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


Gary''s Student wrote...
....
Function superfoo(r As Range) As String
Dim s As String
m = Application.Max(r)
superfoo = r.Find(What:=m, After:=ActiveCell).Address
End Function

This throws a #VALUE! error.

What am I doing wrong?
Click to expand...
....

Find is a *method* rather than a property of the Range class. You can't
run methods from udfs because udfs can't do anything other than return
values to the calling cell. The Find method tries to change ActiveCell,
but udfs can't do that.
 
Depends on the version of excel.

..find won't work with xl97.

..find works with xl2002+

I've blacked out what happens with xl2k (stupid brain!).
 
Thank you Harlan & Jim (once again!)

I don't know if superfoo2 is perfect, but it is a heck of a lot better than
what I originally posted.
 
Select the range and type "D" in the name box (next to formula bar) as
in Harlan's example.

Then copy and paste this into the name box or Edit > Goto (F5) box to
select the first matching cell:

INDIRECT(TEXT(MIN(IF(D=MAX(D),ROW(D)*1000+COLUMN(D))),"R#C##0"),0)

As a shortcut, define this formula as a name e.g. MaxD (with a leading
= sign) and then type MaxD in the name box.

(Note: For Excel 2007 you would need to add a couple of 0's for the
extra columns)
 
Dave,
Thanks for that.
I just tried it in XL2000 and it does not work there.
However, that will not be a problem after the entire world upgrades. <g>
--
Jim Cone


"Dave Peterson" <[email protected]>
wrote in message
Depends on the version of excel.
..find won't work with xl97.
..find works with xl2002+
I've blacked out what happens with xl2k (stupid brain!).
 
Hello,

I use this UDF (Excel 2002):
Function locmax(r As Range) As String
'Locates all max values of a given range
'and returns their addresses as a comma-
'separated string.
'PB V0.9
Dim dmax As Double
Dim lcount As Long
Dim ri As Range
Dim sresult As String, sdel As String, sfirst As String

dmax = Application.WorksheetFunction.Max(r)
Set ri = r.Find(what:=dmax, LookIn:=xlValues)
sfirst = ri.Address(0, 0)
Do
sresult = sresult & sdel & ri.Address(0, 0)
Set ri = r.Find(what:=dmax, after:=ri, LookIn:=xlValues)
sdel = ","
Loop Until ri.Address(0, 0) = sfirst
locmax = sresult
End Function

HTH,
Bernd
 
Gary''s Student said:
Hi Major:

Sub findmax()
Dim r As Range
Set r = Selection
v = r.Cells(1, 1).Value
For Each r In Selection
If r.Value > v Then
v = r.Value
s = r.Address
End If
Next
MsgBox ("maximum value " & v & " found in cell " & s)
End Sub

This can easily be converted into a function as well.
Click to expand...

Thanks Gary's Student,

This is the first time I have used macros in ExCel. My only problem is
that the message box is not large enough for the result. I get
"maximum value 0.00055 found in cell".
Does MsgBox not expand to fit the message?

Thanks,
Peter.
 
max shouldn't need to be array entered.


MrAcquire said:
Let's say that your 202x202 array of numbers is A2:GT203. In cell A1 type
the following to give you the maximum value in the array:

=max(a2:gt203)

but instead of pressing Enter, hold down the Shift & Control keys and press
Enter. This creates an array formula.

If you want to find what cell it's in, you can highlight the array and use
Edit-Find and enter the max value from A1.
Click to expand...
 
If the largest value is in the first cell, then "s" will be undefined. Need
to preset it to s=r.Cells(1,1).Address outside the loop. Also, if there are
multiple cells each with the largest values, the subroutine will only
identify the first one encountered.
 
Back
Top