Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array | Excel Worksheet Functions | |||
Return a cell value based on specific combinations of cells in an array | Excel Worksheet Functions | |||
referencing the value of a cell containing an array formula | Excel Worksheet Functions | |||
return array result in cell based on comparing dates | Excel Worksheet Functions | |||
Entering array in single cell | Excel Discussion (Misc queries) |