Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula to Identify Column Number
Hi--
I have a range of cells J8:T8. The following formula returns the address of the cell with the highest value in that range: =ADDRESS(8,9+MATCH(MAX(J8:T8),J8:T8,0),4) Does anyone know how I might modify this so that it returns only the column number in the address? If, for example, the highest value is in cell R8, I need the formula to return only "R". I know this sounds stupid, but if anyone can assist, I'd be very appreciative! Michael Link Hapless low-level corporate functionary |
#2
|
|||
|
|||
=LEFT(Your_Formula)
Not really flexible though as will fall over if you went past Z in your ranges, but works for your example. Do you need to go past Z? -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "Michael Link" wrote in message ... Hi-- I have a range of cells J8:T8. The following formula returns the address of the cell with the highest value in that range: =ADDRESS(8,9+MATCH(MAX(J8:T8),J8:T8,0),4) Does anyone know how I might modify this so that it returns only the column number in the address? If, for example, the highest value is in cell R8, I need the formula to return only "R". I know this sounds stupid, but if anyone can assist, I'd be very appreciative! Michael Link Hapless low-level corporate functionary |
#3
|
|||
|
|||
Thanks, Ken! Actually, this will work fine--my range will never go past "z,"
so your idea works just spiffily! Excellent! M. Link Grateful functionary "Ken Wright" wrote: =LEFT(Your_Formula) Not really flexible though as will fall over if you went past Z in your ranges, but works for your example. Do you need to go past Z? -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------Â*------------------------------Â*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------Â*------------------------------Â*---------------- "Michael Link" wrote in message ... Hi-- I have a range of cells J8:T8. The following formula returns the address of the cell with the highest value in that range: =ADDRESS(8,9+MATCH(MAX(J8:T8),J8:T8,0),4) Does anyone know how I might modify this so that it returns only the column number in the address? If, for example, the highest value is in cell R8, I need the formula to return only "R". I know this sounds stupid, but if anyone can assist, I'd be very appreciative! Michael Link Hapless low-level corporate functionary |
#4
|
|||
|
|||
One way:
=SUBSTITUTE(LEFT(ADDRESS(1,COLUMN(INDEX(J8:T8,MATC H(MAX(J8:T8),J8:T8,0))),2),2),"$","") Another (array-entered: Ctrl+Shift+Enter): =SUBSTITUTE(LEFT(ADDRESS(1,MIN(IF(J8:T8=MAX(J8:T8) ,COLUMN(J8:T8))),2),2),"$","") Regards, KL "Michael Link" wrote in message ... Hi-- I have a range of cells J8:T8. The following formula returns the address of the cell with the highest value in that range: =ADDRESS(8,9+MATCH(MAX(J8:T8),J8:T8,0),4) Does anyone know how I might modify this so that it returns only the column number in the address? If, for example, the highest value is in cell R8, I need the formula to return only "R". I know this sounds stupid, but if anyone can assist, I'd be very appreciative! Michael Link Hapless low-level corporate functionary |
#5
|
|||
|
|||
If you wanted slightly shorter, and the range is from J, then you could use
=CHAR(73+MATCH(MAX(J8:T8),J8:T8,0)) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "Michael Link" wrote in message ... Thanks, Ken! Actually, this will work fine--my range will never go past "z," so your idea works just spiffily! Excellent! M. Link Grateful functionary "Ken Wright" wrote: =LEFT(Your_Formula) Not really flexible though as will fall over if you went past Z in your ranges, but works for your example. Do you need to go past Z? -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "Michael Link" wrote in message ... Hi-- I have a range of cells J8:T8. The following formula returns the address of the cell with the highest value in that range: =ADDRESS(8,9+MATCH(MAX(J8:T8),J8:T8,0),4) Does anyone know how I might modify this so that it returns only the column number in the address? If, for example, the highest value is in cell R8, I need the formula to return only "R". I know this sounds stupid, but if anyone can assist, I'd be very appreciative! Michael Link Hapless low-level corporate functionary |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
Write formula to add "1" to each number in a column | Excel Worksheet Functions | |||
How can I sort an entire spreadsheet from a list | Excel Worksheet Functions |