 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / September 2006Show which cell has MAX, MIN values?
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 > 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, > [quoted text clipped - 19 lines] >> >> 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 > Ed, > [quoted text clipped - 19 lines] >> >> 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 > Sorry for the accidental but blank reply. > [quoted text clipped - 28 lines] >>> >>> 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 > You have an extra space here: > [quoted text clipped - 43 lines] >>>> >>>> Ed 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 > 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.