| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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, |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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, |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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, |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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, |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Column picked randomly with probability relative to number of entr | Excel Worksheet Functions | |||
| Finding the closest number in column A and take the value in column B | Excel Worksheet Functions | |||
| Conditional Format as a MACRO | Excel Worksheet Functions | |||
| IF/AND/OR/DATEIF Issue...sorry...long post... | Excel Worksheet Functions | |||
| Running total w/2 columns - Excel | Excel Worksheet Functions | |||
Hybrid Mode
