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

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

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

"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)))

"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 ?

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 ?

"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