Using LARGE Function
 

I have a chart of numbers, lets say it is 10 by 10 with different amounts in
each. I want to find which column, for each row, has the Largest number. I
know I need to use the LARGE formula but I don't want it to tell me what the
number is but which column it is in.

Example
A B C D E F G H I
1 2 3 4 5 NUM LRG
1 09 12 08 04 15 1 5
2 17 02 08 20 03 2 4
3 02 01 07 05 03 3 3
4 25 06 17 18 09 4 1

I would then have column H have 1 - 4 and Column I would have which row the
largest number in:

I know I can use the LARGE formula like =LARGE(B2:F2,1) to give me which
number is largest but how do I then tell it so show me which column in B1:F1?

Using LARGE Function
 

I just tested the following, assuming a table with range A1:D9:

=ADDRESS(ROW(A1),MATCH(MAX(A1:D1),A1:D1))

Fill down as necessary. Modify to suit your range of data.

Dave
--
Brevity is the soul of wit.


"David" wrote:

I have a chart of numbers, lets say it is 10 by 10 with different amounts in
each. I want to find which column, for each row, has the Largest number. I
know I need to use the LARGE formula but I don't want it to tell me what the
number is but which column it is in.

Example
A B C D E F G H I
1 2 3 4 5 NUM LRG
1 09 12 08 04 15 1 5
2 17 02 08 20 03 2 4
3 02 01 07 05 03 3 3
4 25 06 17 18 09 4 1

I would then have column H have 1 - 4 and Column I would have which row the
largest number in:

I know I can use the LARGE formula like =LARGE(B2:F2,1) to give me which
number is largest but how do I then tell it so show me which column in B1:F1?

Using LARGE Function
 

Note this returns the cell reference, not the column itself. If you just
want the column returned, you could modify it:

=MID(=ADDRESS(ROW(A1),MATCH(MAX(A1:D1),A1:D1)),2,1 ) (since the ADDRESS
function returns absolute references, i.e., $D$1, etc.)
--
Brevity is the soul of wit.


"Dave F" wrote:

I just tested the following, assuming a table with range A1:D9:

=ADDRESS(ROW(A1),MATCH(MAX(A1:D1),A1:D1))

Fill down as necessary. Modify to suit your range of data.

Dave
--
Brevity is the soul of wit.


"David" wrote:

I have a chart of numbers, lets say it is 10 by 10 with different amounts in
each. I want to find which column, for each row, has the Largest number. I
know I need to use the LARGE formula but I don't want it to tell me what the
number is but which column it is in.

Example
A B C D E F G H I
1 2 3 4 5 NUM LRG
1 09 12 08 04 15 1 5
2 17 02 08 20 03 2 4
3 02 01 07 05 03 3 3
4 25 06 17 18 09 4 1

I would then have column H have 1 - 4 and Column I would have which row the
largest number in:

I know I can use the LARGE formula like =LARGE(B2:F2,1) to give me which
number is largest but how do I then tell it so show me which column in B1:F1?

Using LARGE Function
 

=INDEX($B$1:$F$1,MATCH(MAX($B2:$F2),$B2:$F2,0))
drag down as far as needed

"David" wrote:

I have a chart of numbers, lets say it is 10 by 10 with different amounts in
each. I want to find which column, for each row, has the Largest number. I
know I need to use the LARGE formula but I don't want it to tell me what the
number is but which column it is in.

Example
A B C D E F G H I
1 2 3 4 5 NUM LRG
1 09 12 08 04 15 1 5
2 17 02 08 20 03 2 4
3 02 01 07 05 03 3 3
4 25 06 17 18 09 4 1

I would then have column H have 1 - 4 and Column I would have which row the
largest number in:

I know I can use the LARGE formula like =LARGE(B2:F2,1) to give me which
number is largest but how do I then tell it so show me which column in B1:F1?

Using LARGE Function
 

Thank you for the assistance but the MID function is for text strings and
every time I adjust it I get a B, or C, or T. I was looking for something
that uses the LARGE function that would give the number in the column row...
I would like to use the LARGE function as I can later adjust it for the
Largest, second largest, 10th largest and it has a bit of flexibility.

Thank You though David as I hadn't thought down the road of Address and MID
before and I learned something new.


"Dave F" wrote:

Note this returns the cell reference, not the column itself. If you just
want the column returned, you could modify it:

=MID(=ADDRESS(ROW(A1),MATCH(MAX(A1:D1),A1:D1)),2,1 ) (since the ADDRESS
function returns absolute references, i.e., $D$1, etc.)
--
Brevity is the soul of wit.


"Dave F" wrote:

I just tested the following, assuming a table with range A1:D9:

=ADDRESS(ROW(A1),MATCH(MAX(A1:D1),A1:D1))

Fill down as necessary. Modify to suit your range of data.

Dave
--
Brevity is the soul of wit.


"David" wrote:

I have a chart of numbers, lets say it is 10 by 10 with different amounts in
each. I want to find which column, for each row, has the Largest number. I
know I need to use the LARGE formula but I don't want it to tell me what the
number is but which column it is in.

Example
A B C D E F G H I
1 2 3 4 5 NUM LRG
1 09 12 08 04 15 1 5
2 17 02 08 20 03 2 4
3 02 01 07 05 03 3 3
4 25 06 17 18 09 4 1

I would then have column H have 1 - 4 and Column I would have which row the
largest number in:

I know I can use the LARGE formula like =LARGE(B2:F2,1) to give me which
number is largest but how do I then tell it so show me which column in B1:F1?

Using LARGE Function
 

Hi Dave

(since the ADDRESS
function returns absolute references, i.e., $D$1, etc.)

FYI, Address has a 4th parameter.
Omitted or 1 returns absolutes as you say $D$1
2 returns Absolute Row, Relative Column D$1
3 returns Relative Row, Absolute Column $D1
4 returns Relative address D1

And since we are dealing with a small number of columns (less than 26),
the letter of the column could be obtained an alternative way with

=CHAR(MATCH(MAX(A1:D1),A1:D1)+64)

--
Regards

Roger Govier


"Dave F" wrote in message
...
Note this returns the cell reference, not the column itself. If you
just
want the column returned, you could modify it:

=MID(=ADDRESS(ROW(A1),MATCH(MAX(A1:D1),A1:D1)),2,1 ) (since the ADDRESS
function returns absolute references, i.e., $D$1, etc.)
--
Brevity is the soul of wit.


"Dave F" wrote:

I just tested the following, assuming a table with range A1:D9:

=ADDRESS(ROW(A1),MATCH(MAX(A1:D1),A1:D1))

Fill down as necessary. Modify to suit your range of data.

Dave
--
Brevity is the soul of wit.


"David" wrote:

I have a chart of numbers, lets say it is 10 by 10 with different
amounts in
each. I want to find which column, for each row, has the Largest
number. I
know I need to use the LARGE formula but I don't want it to tell me
what the
number is but which column it is in.

Example
A B C D E F G H I
1 2 3 4 5 NUM LRG
1 09 12 08 04 15 1 5
2 17 02 08 20 03 2 4
3 02 01 07 05 03 3 3
4 25 06 17 18 09 4 1

I would then have column H have 1 - 4 and Column I would have which
row the
largest number in:

I know I can use the LARGE formula like =LARGE(B2:F2,1) to give me
which
number is largest but how do I then tell it so show me which column
in B1:F1?

Using LARGE Function
 

Hi David

If it is the column Number you want then
=MATCH(LARGE(A1:D1,1),A1:D1)

--
Regards

Roger Govier


"David" wrote in message
...
Thank you for the assistance but the MID function is for text strings
and
every time I adjust it I get a B, or C, or T. I was looking for
something
that uses the LARGE function that would give the number in the column
row...
I would like to use the LARGE function as I can later adjust it for
the
Largest, second largest, 10th largest and it has a bit of flexibility.

Thank You though David as I hadn't thought down the road of Address
and MID
before and I learned something new.


"Dave F" wrote:

Note this returns the cell reference, not the column itself. If you
just
want the column returned, you could modify it:

=MID(=ADDRESS(ROW(A1),MATCH(MAX(A1:D1),A1:D1)),2,1 ) (since the
ADDRESS
function returns absolute references, i.e., $D$1, etc.)
--
Brevity is the soul of wit.


"Dave F" wrote:

I just tested the following, assuming a table with range A1:D9:

=ADDRESS(ROW(A1),MATCH(MAX(A1:D1),A1:D1))

Fill down as necessary. Modify to suit your range of data.

Dave
--
Brevity is the soul of wit.


"David" wrote:

I have a chart of numbers, lets say it is 10 by 10 with different
amounts in
each. I want to find which column, for each row, has the Largest
number. I
know I need to use the LARGE formula but I don't want it to tell
me what the
number is but which column it is in.

Example
A B C D E F G H I
1 2 3 4 5 NUM LRG
1 09 12 08 04 15 1 5
2 17 02 08 20 03 2 4
3 02 01 07 05 03 3 3
4 25 06 17 18 09 4 1

I would then have column H have 1 - 4 and Column I would have
which row the
largest number in:

I know I can use the LARGE formula like =LARGE(B2:F2,1) to give
me which
number is largest but how do I then tell it so show me which
column in B1:F1?

Using LARGE Function
 

Thank You. INDEX works for the MAX number in the list and I am sure I can
modify it slightly in case they want second largest number or 10th. If anyone
knows of it right off please send it but Thank You for this point in the
right direction.

"Teethless mama" wrote:

=INDEX($B$1:$F$1,MATCH(MAX($B2:$F2),$B2:$F2,0))
drag down as far as needed

"David" wrote:

I have a chart of numbers, lets say it is 10 by 10 with different amounts in
each. I want to find which column, for each row, has the Largest number. I
know I need to use the LARGE formula but I don't want it to tell me what the
number is but which column it is in.

Example
A B C D E F G H I
1 2 3 4 5 NUM LRG
1 09 12 08 04 15 1 5
2 17 02 08 20 03 2 4
3 02 01 07 05 03 3 3
4 25 06 17 18 09 4 1

I would then have column H have 1 - 4 and Column I would have which row the
largest number in:

I know I can use the LARGE formula like =LARGE(B2:F2,1) to give me which
number is largest but how do I then tell it so show me which column in B1:F1?

Using LARGE Function
 

Got it:
=INDEX(Chart!$B$1:$BD$1,MATCH(LARGE(Chart!$B2:$BD2 ,1),Chart!$B2:$BD2,0))

Thank You Teethless mama. :)

"Teethless mama" wrote:

=INDEX($B$1:$F$1,MATCH(MAX($B2:$F2),$B2:$F2,0))
drag down as far as needed

"David" wrote:

I have a chart of numbers, lets say it is 10 by 10 with different amounts in
each. I want to find which column, for each row, has the Largest number. I
know I need to use the LARGE formula but I don't want it to tell me what the
number is but which column it is in.

Example
A B C D E F G H I
1 2 3 4 5 NUM LRG
1 09 12 08 04 15 1 5
2 17 02 08 20 03 2 4
3 02 01 07 05 03 3 3
4 25 06 17 18 09 4 1

I would then have column H have 1 - 4 and Column I would have which row the
largest number in:

I know I can use the LARGE formula like =LARGE(B2:F2,1) to give me which
number is largest but how do I then tell it so show me which column in B1:F1?

Using LARGE Function
 

All good points.
--
Brevity is the soul of wit.


"Roger Govier" wrote:

Hi Dave

(since the ADDRESS
function returns absolute references, i.e., $D$1, etc.)

FYI, Address has a 4th parameter.
Omitted or 1 returns absolutes as you say $D$1
2 returns Absolute Row, Relative Column D$1
3 returns Relative Row, Absolute Column $D1
4 returns Relative address D1

And since we are dealing with a small number of columns (less than 26),
the letter of the column could be obtained an alternative way with

=CHAR(MATCH(MAX(A1:D1),A1:D1)+64)

--
Regards

Roger Govier


"Dave F" wrote in message
...
Note this returns the cell reference, not the column itself. If you
just
want the column returned, you could modify it:

=MID(=ADDRESS(ROW(A1),MATCH(MAX(A1:D1),A1:D1)),2,1 ) (since the ADDRESS
function returns absolute references, i.e., $D$1, etc.)
--
Brevity is the soul of wit.


"Dave F" wrote:

I just tested the following, assuming a table with range A1:D9:

=ADDRESS(ROW(A1),MATCH(MAX(A1:D1),A1:D1))

Fill down as necessary. Modify to suit your range of data.

Dave
--
Brevity is the soul of wit.


"David" wrote:

I have a chart of numbers, lets say it is 10 by 10 with different
amounts in
each. I want to find which column, for each row, has the Largest
number. I
know I need to use the LARGE formula but I don't want it to tell me
what the
number is but which column it is in.

Example
A B C D E F G H I
1 2 3 4 5 NUM LRG
1 09 12 08 04 15 1 5
2 17 02 08 20 03 2 4
3 02 01 07 05 03 3 3
4 25 06 17 18 09 4 1

I would then have column H have 1 - 4 and Column I would have which
row the
largest number in:

I know I can use the LARGE formula like =LARGE(B2:F2,1) to give me
which
number is largest but how do I then tell it so show me which column
in B1:F1?

Using LARGE Function
 

Hi David

Maybe I confused you with my earlier posting.
I can now see that row 1 contains the numbers 1 to 5. These are not
necessary to obtain the column number, as we are treating B as 1 by
starting with column B

=MATCH(LARGE(B2:F2),B2:F2))
will return 5 (as opposed to 6, being the column number for column F)

--
Regards

Roger Govier


"David" wrote in message
...
Thank You. INDEX works for the MAX number in the list and I am sure I
can
modify it slightly in case they want second largest number or 10th. If
anyone
knows of it right off please send it but Thank You for this point in
the
right direction.

"Teethless mama" wrote:

=INDEX($B$1:$F$1,MATCH(MAX($B2:$F2),$B2:$F2,0))
drag down as far as needed

"David" wrote:

I have a chart of numbers, lets say it is 10 by 10 with different
amounts in
each. I want to find which column, for each row, has the Largest
number. I
know I need to use the LARGE formula but I don't want it to tell me
what the
number is but which column it is in.

Example
A B C D E F G H I
1 2 3 4 5 NUM LRG
1 09 12 08 04 15 1 5
2 17 02 08 20 03 2 4
3 02 01 07 05 03 3 3
4 25 06 17 18 09 4 1

I would then have column H have 1 - 4 and Column I would have which
row the
largest number in:

I know I can use the LARGE formula like =LARGE(B2:F2,1) to give me
which
number is largest but how do I then tell it so show me which column
in B1:F1?