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(OFFSE T(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?

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

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

--
Kevin Vaughn


"AG" wrote:

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(OFFSE T(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?

This works for; nice & simple, too!

"AG" wrote:

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(OFFSE T(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?

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

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

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

In other words if the reference value is "$A$5" you just want the 5 at
the end. If the reference value is "$A$15" you want the 15 at the end
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 string
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 greater
than 4" of the first statement. And continue on until you have the max
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),"Error
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 until
Monday morning)

HTH,
Gary


--
GaryE
Posted from - http://www.officehelp.in

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

"AG" wrote:

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(OFFSE T(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?

No Problem. Glad I could help (kind of :-).

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

Gary


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

"AG" wrote:

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(OFFSE T(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?



--
GaryE
Posted from - http://www.officehelp.in