Show which cell has MAX, MIN values?

[Ed]

At the bottom of a couple thousand rows of data, I have =MAX and =MIN
formulas. Is there some way I could make the cells beneath my MAX and MIN
formulas show me the address of which cell has the displayed MAX or MIN
value? At least the row number?

Ed

 

[Bernie Deitrick]

Ed,

To return the row

=MATCH(cell with Max or Min value,range starting in row 1,false)

or to return the address, say, in Cell N3000, for a value given in N2999

=ADDRESS(MATCH(N2999,N1:NN2998,FALSE),COLUMN(N1))

or to return other matching information, like a name in column A
=INDEX(A:A,MATCH(N2999,N1:NN2998,FALSE))

HTH,
Bernie
MS Excel MVP

 

[Pete_UK]

Use the MATCH function, which returns the relative position of the
matched cell. Assume your values are in A1:A2000, your max value is in
A2002 and your min value is in A2004. Enter this formula in B2002:

=MATCH(A2002,A$1:A$2000,0)

then copy this to B2004.

You might have several values that are the minimum (or maximum) - Match
will find the first in the list. As it gives the relative position,
then if your data starts in A10 instead of A1, then you would have to
add 9 on to give you the row number.

Hope this helps.

Pete

 

[Ed]

Ed,

To return the row

=MATCH(cell with Max or Min value,range starting in row 1,false)

or to return the address, say, in Cell N3000, for a value given in N2999

=ADDRESS(MATCH(N2999,N1:NN2998,FALSE),COLUMN(N1))

or to return other matching information, like a name in column A
=INDEX(A:A,MATCH(N2999,N1:NN2998,FALSE))

HTH,
Bernie
MS Excel MVP

 

[Ed]

Sorry for the accidental but blank reply.

I tried the ADDRESS formula, but came up with a #NAME error??
=ADDRESS(MATCH(U4604,U$5:U$4597,FALSE),COLUMN (U$1))
You gave

=ADDRESS(MATCH(N2999,N1:NN2998,FALSE),COLUMN(N1))

which I tried to copy well, but must have done something wrong??

Ed

 

[Bernie Deitrick]

You have an extra space here:

COLUMN (U$1))

but you should use

=ADDRESS(MATCH(U4604,U$1:U$4597,FALSE),COLUMN(U$1))
or
=ADDRESS(MATCH(U4604,U$5:U$4597,FALSE) +4,COLUMN(U$1))

HTH,
Bernie
MS Excel MVP

 

[Ed]

Thank you, Bernie! Taking out the space and adding the +4 did the trick.
The +4 threw me at first, then I read up on MATCH and realized it returned
the _relative_ position, which would be off since I start in row 5 vs row 1.

Thanks for the boost.
Ed