Show which cell has MAX, MIN values?

  • Thread starter Thread starter Ed
  • Start date Start date
E

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
 
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
 
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
 
Bernie Deitrick said:
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
Click to expand...
 
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))
Click to expand...
which I tried to copy well, but must have done something wrong??

Ed
 
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
 
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
 
Back
Top