Cell value using Max

[AG]

Looking for a formula that will give the value of the leftmost cell (column
A), 1 row up from a cell containing the maximum value in a table of values
contained in the range $B$5:$AF$97, named rng.

I can find the address of the max value via the array formula:

=CELL("Address",INDEX(rng,MATCH(TRUE,COUNTIF(OFFSET(rng,ROW(rng)-CELL("Row",rng),0,1,),MAX(rng))>0,0),MATCH(MAX(rng),INDEX(rng,MATCH(TRUE,COUNTIF(OFFSET(rng,ROW(rng)-CELL("Row",rng),0,1,),MAX(rng))>0,0),0),0)))

So if the max value happened to be in cell $W$57, how would I get the value
of cell $A$56?

 

[Kevin Vaughn]

If the cell returning the address of $w$57 is in B2, try this formula:

=INDIRECT("a"&ROW(INDIRECT(B2))-1)

 

[GaryE]

Did you try to use the offset function? It looks like that might d
exactly what you want. Assuming that you did try or that it won't wor
here is an alternative.

You can use the len() function to determine how many characters are i
the reference. Then use the right() function to grab the number off th
end of the reference (depending upon the lenght of the reference)
Subtract 1. Then use the & feature to merge the text "$A$" with tha
value.

You need to know how long the reference is so that you chop off jus
the number at the end of the reference.

In other words if the reference value is "$A$5" you just want the 5 a
the end. If the reference value is "$A$15" you want the 15 at the en
etc.

ok?


So lets assume that the cell A7 contains the value
$E$9

so in pseudo code that looks like this

if the length is 4
then print "$A$"
add the right most character to the end of that text strin
after subtracting 1
&right(A7,1)-1
else print "Length is greater than 4"

Note that Len(A7) returns: 4
right(A7,1) returns:9
and that right(A7,1)-1 returns: 8

in excel it looks like this:
=if(len(A7)=4,"$A$"&right(A7,1)-1,"Length is greater than 4")

and it will return: $A$8

now if the length is 5 (the cell reference ends in two digits)

so lets say that cell A7 contains $E$14

if(len(A7)=5"$A$"&right(A7,1)-1,"Length is greater than 5"

will return $A$13

so put the second if statement in place of the text "Length is greate
than 4" of the first statement. And continue on until you have the ma
number of rows in your worksheet taken care of.

Assuming that you have less than 1000 rows
use

IF(LEN(A7)=4,"$A$"&RIGHT(A7,1)-1,IF(LEN(A7)=5,"$A$"&RIGHT(A7,2)-1,"$A$"&RIGHT(A7,3)-1),"Erro
the reference contains more than 999 rows")

is that clear as mud?

Any questions let me know!

(I'm doing this from work so I won't be able to get back to you unti
Monday morning)

HTH,
Gar

 

[AG]

This works for; nice & simple, too!

 

[AG]

Thanks for the reply Gary.
Your solution might work but see Kevin's reply for a simplier option.

 

[GaryE]

No Problem. Glad I could help (kind of .

FWIW the solution I posted will work -- I tested it thoroughly

Gary