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.
--
Gary's Student


" wrote:

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.

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,FAL SE),2))

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




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()

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.

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.

" wrote:

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


"Harlan Grove" wrote:

Gary''s Student wrote...
Sub findmax()

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.

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.

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

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


"Harlan Grove" wrote in message ups.com...
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?
....

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!).

Jim Cone wrote:

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

"Harlan Grove" wrote in message ups.com...
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?
...

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.

--

Dave Peterson

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.
--
Gary's Student


"Jim Cone" wrote:

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


"Harlan Grove" wrote in message ups.com...
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?
....

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.

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)


wrote:
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.

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"
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!).

Jim Cone wrote:
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


--

Dave Peterson

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

Thanks for posting back. That's the way I (kind of) remember it.

Jim Cone wrote:

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"
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!).

Jim Cone wrote:
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

--

Dave Peterson

--

Dave Peterson

Gary''s Student wrote:
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.
--
Gary's Student

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.



" wrote:

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.

max shouldn't need to be array entered.


"MrAcquire" wrote:

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.

" wrote:

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.