Identifying the largest number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am in need of some help regarding the creation of a simple MAX calculation
in my code.

The data is something like the following:

Col I Col J Col K Col L
12 4 7 19

How would I write a line of code that will identify that within this array
Column L has the largest figure?

Many thanks
 
Hello Phil

Msgbox "Max value is in " & _
Evaluate("=ADDRESS(1,MATCH(MAX(I1:L1),I1:L1,0))")

HTH
Cordially
Pascal
 
Phil
Forget my answer, it will not return the correct address.
I will have another look at it.

Cordially
Pascal
 
A function would do it, assuming your data are in the named range myRange:-

Function MaxAddress(myRange)
MaxNum = Application.Max(myRange)
For Each Cell In myRange
If Cell = MaxNum Then
MaxAddress = Cell.Address
Exit For
End If
Next Cell

call the funxtion with =maxaddress(myrange) typed in any cell

Mike
End Function
 
Ok, this should work better:
MsgBox "Max value is in " & _
Evaluate("=ADDRESS(ROW(A1),MATCH(MAX(1:1),1:1,0))")

HTH
Cordially
Pascal
 
Hi Pascal

I think modifying your posting to
MsgBox "Max value is in " & _
Evaluate("=ADDRESS(1,MATCH(MAX(I1:L1),I1:L1,0)+column(I1)-1)")

will resolve the matter
 
Hi Roger
Yes definitely ;-)

Cordially
Pascal

Roger Govier said:
Hi Pascal

I think modifying your posting to
MsgBox "Max value is in " & _
Evaluate("=ADDRESS(1,MATCH(MAX(I1:L1),I1:L1,0)+column(I1)-1)")

will resolve the matter
Click to expand...
 
Hi Pascal

Wouldn't that return the address of the highest value in row 1, even it
were outside the range that the OP wanted?
 
Yes Roger it would.
Since our friend did not specify (although it seems from his original post
that he is looking in a row), he may need to amend to his needs.

Cordially
Pascal
 
Good responses, but you don't really need a new function for this.
Assuming that the values are in row 2, you could place the following
formula in M2.
=ADDRESS(ROW(),COLUMN(INDEX(I2:L2,MATCH(MAX(I2:L2),I2:L2,0))))
That would return an absolute reference such as $L$2. You could then
use the Mid function to pull out the column letter.
=MID(ADDRESS(ROW(),COLUMN(INDEX(I2:L2,MATCH(MAX(I2:L2),I2:L2,0)))),2,
(FIND("$",ADDRESS(ROW(),COLUMN(INDEX(I2:L2,MATCH(MAX(I2:L2),I2:L2,0)))),
2)-2))

Pretty complex formula, but it works.

HTH
 
Hi

I hadn't noticed that the Op wanted just the column letter.
That being the case then
=SUBSTITUTE(ADDRESS(1,MATCH(MAX(I1:L1),I1:L1,0)+COLUMN(I1)-1,4),"1","")

will achieve that in a shorter form

--
Regards

Roger Govier


Good responses, but you don't really need a new function for this.
Assuming that the values are in row 2, you could place the following
formula in M2.
=ADDRESS(ROW(),COLUMN(INDEX(I2:L2,MATCH(MAX(I2:L2),I2:L2,0))))
That would return an absolute reference such as $L$2. You could then
use the Mid function to pull out the column letter.
=MID(ADDRESS(ROW(),COLUMN(INDEX(I2:L2,MATCH(MAX(I2:L2),I2:L2,0)))),2,
(FIND("$",ADDRESS(ROW(),COLUMN(INDEX(I2:L2,MATCH(MAX(I2:L2),I2:L2,0)))),
2)-2))

Pretty complex formula, but it works.

HTH
 
Many Thanks for all of your suggestions. I think ultimately what I was trying
to do was too complicated for the needs of the workbook.

I have used some of what has been proposed but have also created a workround.

Thanks again for all of your help.
 
Back
Top