Cell ref of max value in array
 

Using XL2002. Other answers in these discussions have given me nearly what
I'm after; please would someone complete this?
A large array of numbers: which cell in each row has the max value?
=MATCH(MAX(range),range,0) gets me its position, e.g. 44th cell along, but
how can I translate that to Column AR or cell ref AR6?

Cell ref of max value in array
 

Try this on for size - if it doesn't work, let me know and we can tweak
it. Later, Chad

=ADDRESS(MATCH(MAX(range),range,0)+ROW(range)-1,COLUMN(range),1)


--
cvolkert
------------------------------------------------------------------------
cvolkert's Profile: http://www.excelforum.com/member.php...o&userid=24380
View this thread: http://www.excelforum.com/showthread...hreadid=489664

Cell ref of max value in array
 

I only just asked the question at (my) midnight, and I found your answer when
I arrived at my desk this morning! Thank you very much!

I think I've cracked it because of your help. Sadly I couldn't get yours to
work as you intended but I think yours when tweaked would give a much slicker
way than I have now used.

If you wish to check me it all follows, if not just ignore it and thanks
again!

Given one row from (say) A5:E5 and the max value is in C5,
=MATCH(MAX(A5:E5),A5:E5,0) returns 3 which is correct.
So need to get the address which will be 3 = C and 5 is already fixed, so
C$5, so ADDRESS(absolute_row,relative_col,2) should work, so I tried
=ADDRESS(2,MATCH(MAX(A2:E2),A2:E2,0),2). That worked except the first 2 for
the row number won't change to 3,4,5 etc.
So change the 2 to ROW() and it came out
=ADDRESS(ROW(),MATCH(MAX(A2:E2),A2:E2,0),2)
This has the disadvantage that I can't name the entire range, but it does
mean I can add on rows at the bottom and just autofill the formula down.
Finally, as the range being checked started far away from column A, I added
45 to get it to start counting from the column AT rather than A

"cvolkert" wrote:


Try this on for size - if it doesn't work, let me know and we can tweak
it. Later, Chad

=ADDRESS(MATCH(MAX(range),range,0)+ROW(range)-1,COLUMN(range),1)


--
cvolkert
------------------------------------------------------------------------
cvolkert's Profile: http://www.excelforum.com/member.php...o&userid=24380
View this thread: http://www.excelforum.com/showthread...hreadid=489664

Cell ref of max value in array
 

Glad I could help. I had assumed 'range' was a column instead of a row.
For that reason, the logic in my formula was the reverse of what you
needed. Glad you got to the right answer.


--
cvolkert
------------------------------------------------------------------------
cvolkert's Profile: http://www.excelforum.com/member.php...o&userid=24380
View this thread: http://www.excelforum.com/showthread...hreadid=489664