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

=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

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

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

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