Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#2
![]()
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
![]()
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. |
#3
![]()
Posted to microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#4
![]()
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
![]()
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. |
#5
![]()
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
![]()
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. |
#6
![]()
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
![]()
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. |
#7
![]()
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
![]()
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. |
#8
![]()
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
![]()
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. |
#9
![]()
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
![]()
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 |
#10
![]()
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
![]()
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. |
#11
![]()
Posted to microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#12
![]()
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
![]()
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 |
#13
![]()
Posted to microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#14
![]()
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
![]()
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 |
#15
![]()
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
![]()
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. |
#16
![]()
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
![]()
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Finding Location of Maximum Value in 2D Array | Excel Discussion (Misc queries) | |||
How to know location of cell found with MIN/MAX? | Excel Discussion (Misc queries) | |||
Question to Bob Phillips (or whoever...) | Excel Worksheet Functions | |||
Return cell contents based on conditional lookup | Excel Worksheet Functions | |||
Stock Location Sorting Problem | Excel Discussion (Misc queries) |