ADDRESS function
 

Yesterday someone was fortunate enough to provide me with the following
formula, which returns the address of the cell with the maximum value in a
certain range:

=ADDRESS(8,9+MATCH(MAX(J8:T8),J8:T8,0))

The formula, however, returns absolute cell addresses, and I need it to
return relative addresses. I've tried modifying it like this:

=ADDRESS(8,9,4+MATCH(MAX(J8:T8),J8:T8,0))

but when I do, the cell in which it's entered seems to think it's text and
leaves the entire thing in the cell. (It doesn't even generate any error
messages--it just sits in the cell like I would.) Help! Any ideas what I'm
doing wrong?

M. Link

Try

=ADDRESS(8,9+MATCH(MAX(J8:T8),J8:T8,0),4)

Regards
Rowan

"Michael Link" wrote:

Yesterday someone was fortunate enough to provide me with the following
formula, which returns the address of the cell with the maximum value in a
certain range:

=ADDRESS(8,9+MATCH(MAX(J8:T8),J8:T8,0))

The formula, however, returns absolute cell addresses, and I need it to
return relative addresses. I've tried modifying it like this:

=ADDRESS(8,9,4+MATCH(MAX(J8:T8),J8:T8,0))

but when I do, the cell in which it's entered seems to think it's text and
leaves the entire thing in the cell. (It doesn't even generate any error
messages--it just sits in the cell like I would.) Help! Any ideas what I'm
doing wrong?

M. Link

Thanks, Rowan! That did it! Excellent!

M. Link

"Rowan" wrote:

Try

=ADDRESS(8,9+MATCH(MAX(J8:T8),J8:T8,0),4)

Regards
Rowan

"Michael Link" wrote:

Yesterday someone was fortunate enough to provide me with the following
formula, which returns the address of the cell with the maximum value in a
certain range:

=ADDRESS(8,9+MATCH(MAX(J8:T8),J8:T8,0))

The formula, however, returns absolute cell addresses, and I need it to
return relative addresses. I've tried modifying it like this:

=ADDRESS(8,9,4+MATCH(MAX(J8:T8),J8:T8,0))

but when I do, the cell in which it's entered seems to think it's text and
leaves the entire thing in the cell. (It doesn't even generate any error
messages--it just sits in the cell like I would.) Help! Any ideas what I'm
doing wrong?

M. Link