offsets (I think)

S

Stan

I have found a value using the MAX formula and would now like to return the
value found in the cell one row above and one column before the original
position of this result. Is this possible?
 
J

joel

this is a 3 step process.
1) Find the max value
2) Use Match to locate the offset of the value in the array
3) Use offset to get the value you are looking for.


You should look at chip Pearson webpage. He has lots of excel formul
that are very usful when you not sure which formula to use


'Excel Redirect' (http://www.cpearson.com
 
T

T. Valko

Here's one way...

Assume this is your data:

...........A..........B
1........A.........22
2........B.........17
3........C.........44
4........D.........12
5........E..........15

=INDEX(A1:A5,MATCH(MAX(B1:B5),B1:B5,0)-1)

This will work correctly *unless* the max value in B1:B5 is located in B1.
So, what result do want if that is the case?
 
J

JLatham

Biff,
A slight mod gives the opportunity to identify, and act on, the max in B1
situation:
=IF(MATCH(MAX(B1:B5),B1:B5,0)=1,"Max is in 1st
row",INDEX(A1:A5,MATCH(MAX(B1:B5),B1:B5,0)-1))
but I figure you already had that in the back of your head. As you asked,
what to do in that case is still up in the air.

Another oddity of this situation is that "E" (A5) can never be returned.
Hopefully that works in with the OPs needs also.
 
S

Stan

Thankyou both Joel and t valko for your prompt replies. with a little playing
I was able to make mr valko's suggestion work satisfactorily
 
Top