Finding the column which a value was drawn from
 

I have a very large spreadsheet that I'm using the max and min functions
on. I need to know which column that the max and min are in. For example I
have

Column A B C D E F
Row 1 1 2 3 4 5 6

THe max function will tell me that the max is 6. What I need is it to tell
me the Max is 6 in Row F.

How do i do this?

Thanks,

Finding the column which a value was drawn from
 

Conditional Formating =MAX($A1:$F1)
--
Kevin


"skier464" wrote:

I have a very large spreadsheet that I'm using the max and min functions
on. I need to know which column that the max and min are in. For example I
have

Column A B C D E F
Row 1 1 2 3 4 5 6

THe max function will tell me that the max is 6. What I need is it to tell
me the Max is 6 in Row F.

How do i do this?

Thanks,

Finding the column which a value was drawn from
 

This will get you the column number for the max value in A3:F3:
=COLUMN(INDIRECT(CELL("ADDRESS",OFFSET(A1,0,MATCH( MAX(A3:F3),A3:F3,0)-1))))

=CELL("ADDRESS",OFFSET(A1,0,MATCH(MAX(A3:F3),A3:F3 ,0)-1))))
will get you the address of the cell. You can use string functions to
isolate the column letters.

Dan

"skier464" wrote:

I have a very large spreadsheet that I'm using the max and min functions
on. I need to know which column that the max and min are in. For example I
have

Column A B C D E F
Row 1 1 2 3 4 5 6

THe max function will tell me that the max is 6. What I need is it to tell
me the Max is 6 in Row F.

How do i do this?

Thanks,

Finding the column which a value was drawn from
 

Thanks it worked great

"Dan Hatola" wrote:

This will get you the column number for the max value in A3:F3:
=COLUMN(INDIRECT(CELL("ADDRESS",OFFSET(A1,0,MATCH( MAX(A3:F3),A3:F3,0)-1))))

=CELL("ADDRESS",OFFSET(A1,0,MATCH(MAX(A3:F3),A3:F3 ,0)-1))))
will get you the address of the cell. You can use string functions to
isolate the column letters.

Dan

"skier464" wrote:

I have a very large spreadsheet that I'm using the max and min functions
on. I need to know which column that the max and min are in. For example I
have

Column A B C D E F
Row 1 1 2 3 4 5 6

THe max function will tell me that the max is 6. What I need is it to tell
me the Max is 6 in Row F.

How do i do this?

Thanks,

Finding the column which a value was drawn from
 

A bit less complicated:

Column number
=MATCH(MAX(A1:F1),A1:F1,0)

Column letter:
=ADDRESS(1,MATCH(MAX(A1:F1),A1:F1,0))
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"skier464" wrote in message
...
Thanks it worked great

"Dan Hatola" wrote:

This will get you the column number for the max value in A3:F3:

=COLUMN(INDIRECT(CELL("ADDRESS",OFFSET(A1,0,MATCH( MAX(A3:F3),A3:F3,0)-1))))

=CELL("ADDRESS",OFFSET(A1,0,MATCH(MAX(A3:F3),A3:F3 ,0)-1))))
will get you the address of the cell. You can use string functions to
isolate the column letters.

Dan

"skier464" wrote:

I have a very large spreadsheet that I'm using the max and min
functions
on. I need to know which column that the max and min are in. For
example I
have

Column A B C D E F
Row 1 1 2 3 4 5 6

THe max function will tell me that the max is 6. What I need is it to
tell
me the Max is 6 in Row F.

How do i do this?

Thanks,

Finding the column which a value was drawn from
 

Great simplification, especially with the address formula. I knew there had
to be an easier way. The only thing to be careful about regarding your
column number solution is that the match function will return the relative
position in the range. Therefore if you don't start with column A, your
column number will have to be adjusted accordingly. The offset formula
(albeit more complicated) allows the starting point to change with the range.
Looking back at my formula, I am not sure why I had A1 instead of A3.

Best,
Dan

"Ragdyer" wrote:

A bit less complicated:

Column number
=MATCH(MAX(A1:F1),A1:F1,0)

Column letter:
=ADDRESS(1,MATCH(MAX(A1:F1),A1:F1,0))
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"skier464" wrote in message
...
Thanks it worked great

"Dan Hatola" wrote:

This will get you the column number for the max value in A3:F3:

=COLUMN(INDIRECT(CELL("ADDRESS",OFFSET(A1,0,MATCH( MAX(A3:F3),A3:F3,0)-1))))

=CELL("ADDRESS",OFFSET(A1,0,MATCH(MAX(A3:F3),A3:F3 ,0)-1))))
will get you the address of the cell. You can use string functions to
isolate the column letters.

Dan

"skier464" wrote:

I have a very large spreadsheet that I'm using the max and min
functions
on. I need to know which column that the max and min are in. For
example I
have

Column A B C D E F
Row 1 1 2 3 4 5 6

THe max function will tell me that the max is 6. What I need is it to
tell
me the Max is 6 in Row F.

How do i do this?

Thanks,