Home |
Search |
Today's Posts |
#1
|
|||
|
|||
LOOKUP & RETURN CELL ADDRESS
What formula would I use to look up the address of the maximum value in a
column and return the cell address not the value? -- Carolan |
#2
|
|||
|
|||
Carolan wrote:
What formula would I use to look up the address of the maximum value in a column and return the cell address not the value? See the formula system I describe in: http://tinyurl.com/dph4d |
#3
|
|||
|
|||
Assume you have a column header in row one, & that cell is named 'hdr'
Assume your column of values is named tbl, and starts in row 2 =ADDRESS(ROW(OFFSET(hdr,MATCH(MAX(tbl),tbl),0)),CO LUMN(hdr)) will give you the address of the FIRST value equal to the max value in the list "Carolan" wrote: What formula would I use to look up the address of the maximum value in a column and return the cell address not the value? -- Carolan |
#4
|
|||
|
|||
"Duke Carey" wrote...
Assume you have a column header in row one, & that cell is named 'hdr' Assume your column of values is named tbl, and starts in row 2 =ADDRESS(ROW(OFFSET(hdr,MATCH(MAX(tbl),tbl),0)),C OLUMN(hdr)) .... MATCH(MAX(tbl),tbl) would only work when tbl is sorted in ascending order, in which case ROWS(tbl) would be much simpler. Perhaps you meant MATCH(MAX(tbl),tbl,0)? More compact to use =CELL("Address",INDEX(tbl,MATCH(MAX(tbl),tbl,0))) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP & RETURN CELL ADDRESS
"Harlan Grove" wrote: "Duke Carey" wrote... Assume you have a column header in row one, & that cell is named 'hdr' Assume your column of values is named tbl, and starts in row 2 =ADDRESS(ROW(OFFSET(hdr,MATCH(MAX(tbl),tbl),0)),C OLUMN(hdr)) .... MATCH(MAX(tbl),tbl) would only work when tbl is sorted in ascending order, in which case ROWS(tbl) would be much simpler. Perhaps you meant MATCH(MAX(tbl),tbl,0)? More compact to use =CELL("Address",INDEX(tbl,MATCH(MAX(tbl),tbl,0))) This works brilliantly, and by substituting 'Row' for 'Address' I can get the row number. Now I want to refer to another cell in that same row, and extract the value from it. I tried: =C(cell("row",index(tbl(match(max(tbl),tbl,0)))) where 'C' is the column from which I want the value, but it doesn't work. Background: My worksheet has twelve month columns, and a total column. Rows are years, 'tbl' is the total column. The formula (as amended) gives me the row containing the highest annual total, and I want to refer to the individual months in that year. Any ideas, please ? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP & RETURN CELL ADDRESS
Hi
Create a named range to cover all of your data, called myData Then use =INDEX(myData,MATCH(MAX(tbl),tbl,0),COLUMN(B1)) to pick up the value for January, assuming that column A holds the Year number and column B holds January data As you copy across, column(B1) will change to C1, D1 etc to give you values for Feb, Mar etc. -- Regards Roger Govier "YellowTump" wrote in message ... "Harlan Grove" wrote: "Duke Carey" wrote... Assume you have a column header in row one, & that cell is named 'hdr' Assume your column of values is named tbl, and starts in row 2 =ADDRESS(ROW(OFFSET(hdr,MATCH(MAX(tbl),tbl),0)),C OLUMN(hdr)) .... MATCH(MAX(tbl),tbl) would only work when tbl is sorted in ascending order, in which case ROWS(tbl) would be much simpler. Perhaps you meant MATCH(MAX(tbl),tbl,0)? More compact to use =CELL("Address",INDEX(tbl,MATCH(MAX(tbl),tbl,0))) This works brilliantly, and by substituting 'Row' for 'Address' I can get the row number. Now I want to refer to another cell in that same row, and extract the value from it. I tried: =C(cell("row",index(tbl(match(max(tbl),tbl,0)))) where 'C' is the column from which I want the value, but it doesn't work. Background: My worksheet has twelve month columns, and a total column. Rows are years, 'tbl' is the total column. The formula (as amended) gives me the row containing the highest annual total, and I want to refer to the individual months in that year. Any ideas, please ? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP & RETURN CELL ADDRESS
"Roger Govier" wrote: Hi Create a named range to cover all of your data, called myData Then use =INDEX(myData,MATCH(MAX(tbl),tbl,0),COLUMN(B1)) to pick up the value for January, assuming that column A holds the Year number and column B holds January data As you copy across, column(B1) will change to C1, D1 etc to give you values for Feb, Mar etc. -- Regards Roger Govier Thank you, that was quick ! It works, but it appears that the 'column(B1)' is referring to a sort of sub-range of columns within 'myData', not to the spreadsheet column of similar name. Is that right ? Regards, Tim Dawson |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP & RETURN CELL ADDRESS
Hi Tim
COLUMN() returns the column number, 1 for column A, 2 for B etc. Specifying COLUMN(B1) is telling the formula to use 2 as the column Offset in the Index of myData. As you drag across, it steps the number up accordingly. It is exactly the same as typing =INDEX(myData,MATCH(MAX(tbl),tbl,0),2) but it saves having to alter the column offset manually as you copy across the page. -- Regards Roger Govier "YellowTump" wrote in message ... "Roger Govier" wrote: Hi Create a named range to cover all of your data, called myData Then use =INDEX(myData,MATCH(MAX(tbl),tbl,0),COLUMN(B1)) to pick up the value for January, assuming that column A holds the Year number and column B holds January data As you copy across, column(B1) will change to C1, D1 etc to give you values for Feb, Mar etc. -- Regards Roger Govier Thank you, that was quick ! It works, but it appears that the 'column(B1)' is referring to a sort of sub-range of columns within 'myData', not to the spreadsheet column of similar name. Is that right ? Regards, Tim Dawson |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP & RETURN CELL ADDRESS
"Roger Govier" wrote: Hi Tim COLUMN() returns the column number, 1 for column A, 2 for B etc. Specifying COLUMN(B1) is telling the formula to use 2 as the column Offset in the Index of myData. As you drag across, it steps the number up accordingly. It is exactly the same as typing =INDEX(myData,MATCH(MAX(tbl),tbl,0),2) but it saves having to alter the column offset manually as you copy across the page. -- Regards Roger Govier Hello Rogar, Thanks, I thought it was something like that. I've been able to copy it right across as you say. I see the significance of the 'B' (B=2), but changing the '1' in B1 to any other row number (e.g. 'B11') makes no difference to the result, so I don't understand what the '1' is for. It's not important in the great scheme of things, but it helps to know what I'm doing, in case I need to do it again ! Regards, Tim Dawson |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP & RETURN CELL ADDRESS
Hi Tim
As you say the row number has no significance in this equation, as we are using the COLUMN() calculation, but we are giving the column function a cell address from which to calculate the column number. =COLUMN(B) on its own, will not work =COLUMN(B:B) will give exactly the same result as COLUMN(B1) =COLUMNS($A:B) will also give a result of 2, counting from the fixed $A to B, and will extend to $A:C, $A:D as we copy across to give 3, 4 etc. note the plural in the above, as this is a count of the columns within the specified range. My preference has always been to give the reference of row 1 for whichever column number I want the numeric value from, hence B1 =ROW(A1) will be 1, regardless of the column letter =ROW(C13) will be 13 -- Regards Roger Govier "YellowTump" wrote in message ... "Roger Govier" wrote: Hi Tim COLUMN() returns the column number, 1 for column A, 2 for B etc. Specifying COLUMN(B1) is telling the formula to use 2 as the column Offset in the Index of myData. As you drag across, it steps the number up accordingly. It is exactly the same as typing =INDEX(myData,MATCH(MAX(tbl),tbl,0),2) but it saves having to alter the column offset manually as you copy across the page. -- Regards Roger Govier Hello Rogar, Thanks, I thought it was something like that. I've been able to copy it right across as you say. I see the significance of the 'B' (B=2), but changing the '1' in B1 to any other row number (e.g. 'B11') makes no difference to the result, so I don't understand what the '1' is for. It's not important in the great scheme of things, but it helps to know what I'm doing, in case I need to do it again ! Regards, Tim Dawson |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP & RETURN CELL ADDRESS
Hello Roger,
Thank you for that full explanation. It's been a long time since I needed to learn about an Excel function that was new to me (although I'm sure there are lots more I know nothing about, but haven't yet needed). It's great to find such quick and authoritative help. Thanks again. Regards, Tim "Roger Govier" wrote: Hi Tim As you say the row number has no significance in this equation, as we are using the COLUMN() calculation, but we are giving the column function a cell address from which to calculate the column number. =COLUMN(B) on its own, will not work =COLUMN(B:B) will give exactly the same result as COLUMN(B1) =COLUMNS($A:B) will also give a result of 2, counting from the fixed $A to B, and will extend to $A:C, $A:D as we copy across to give 3, 4 etc. note the plural in the above, as this is a count of the columns within the specified range. My preference has always been to give the reference of row 1 for whichever column number I want the numeric value from, hence B1 =ROW(A1) will be 1, regardless of the column letter =ROW(C13) will be 13 -- Regards Roger Govier -- Regards Roger Govier Hello Rogar, Thanks, I thought it was something like that. I've been able to copy it right across as you say. I see the significance of the 'B' (B=2), but changing the '1' in B1 to any other row number (e.g. 'B11') makes no difference to the result, so I don't understand what the '1' is for. It's not important in the great scheme of things, but it helps to know what I'm doing, in case I need to do it again ! Regards, Tim Dawson |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP & RETURN CELL ADDRESS
Hello Roger,
Thank you for that full explanation. It's a long time since I needed to learn about an Excel function I hadn't met before, though I'm sure there are plenty more out there that I haven't needed yet. It's great to know there's help out there when one needs it. Thanks again. Regards, Tim Dawson "Roger Govier" wrote: Hi Tim As you say the row number has no significance in this equation, as we are using the COLUMN() calculation, but we are giving the column function a cell address from which to calculate the column number. =COLUMN(B) on its own, will not work =COLUMN(B:B) will give exactly the same result as COLUMN(B1) =COLUMNS($A:B) will also give a result of 2, counting from the fixed $A to B, and will extend to $A:C, $A:D as we copy across to give 3, 4 etc. note the plural in the above, as this is a count of the columns within the specified range. My preference has always been to give the reference of row 1 for whichever column number I want the numeric value from, hence B1 =ROW(A1) will be 1, regardless of the column letter =ROW(C13) will be 13 -- Regards Roger Govier "YellowTump" wrote in message ... |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP & RETURN CELL ADDRESS
When you need to learn about an Excel function that is new to you, try Excel
help. -- David Biddulph "YellowTump" wrote in message ... Hello Roger, Thank you for that full explanation. It's been a long time since I needed to learn about an Excel function that was new to me (although I'm sure there are lots more I know nothing about, but haven't yet needed). It's great to find such quick and authoritative help. Thanks again. "Roger Govier" wrote: Hi Tim As you say the row number has no significance in this equation, as we are using the COLUMN() calculation, but we are giving the column function a cell address from which to calculate the column number. =COLUMN(B) on its own, will not work =COLUMN(B:B) will give exactly the same result as COLUMN(B1) =COLUMNS($A:B) will also give a result of 2, counting from the fixed $A to B, and will extend to $A:C, $A:D as we copy across to give 3, 4 etc. note the plural in the above, as this is a count of the columns within the specified range. My preference has always been to give the reference of row 1 for whichever column number I want the numeric value from, hence B1 =ROW(A1) will be 1, regardless of the column letter =ROW(C13) will be 13 -- Regards Roger Govier -- Regards Roger Govier Hello Rogar, Thanks, I thought it was something like that. I've been able to copy it right across as you say. I see the significance of the 'B' (B=2), but changing the '1' in B1 to any other row number (e.g. 'B11') makes no difference to the result, so I don't understand what the '1' is for. It's not important in the great scheme of things, but it helps to know what I'm doing, in case I need to do it again ! Regards, Tim Dawson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Possible Lookup Table | Excel Worksheet Functions | |||
How do I obtain the address of a cell using the vlookup function? | Excel Worksheet Functions | |||
How do I use a function to return the address of a cell? | Excel Worksheet Functions | |||
I want to return "0" based on another cell | Excel Worksheet Functions | |||
How do I dynamically retrieve the cell address of the last cell t. | Excel Discussion (Misc queries) |