| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel function LARGE returns error 'too few arguments' | Excel Worksheet Functions | |||
| Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
| Large Function | Excel Worksheet Functions | |||
| Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
| Help on Large Function | Excel Worksheet Functions | |||
Hybrid Mode
