Show which cell has MAX, MIN values? "Ed" <ed_millis[ at ]NO_SPAM.yahoo.com> 29.09.2006 18:45:08 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

Re: Show which cell has MAX, MIN values? "Bernie Deitrick" <deitbe [ at ] consumer dot org> 29.09.2006 18:55:58 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" <ed_millis[ at ]NO_SPAM.yahoo.com> wrote in message news:eugtkd$4GHA.4832[ at ]TK2MSFTNGP06.phx.gbl... [Quoted Text] > 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 >

Re: Show which cell has MAX, MIN values? "Pete_UK" <pashurst[ at ]auditel.net> 29.09.2006 19:00:09 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 wrote: [Quoted Text] > 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

Re: Show which cell has MAX, MIN values? "Ed" <ed_millis[ at ]NO_SPAM.yahoo.com> 29.09.2006 19:55:00 "Bernie Deitrick" <deitbe [ at ] consumer dot org> wrote in message news:%23ZnPai$4GHA.3512[ at ]TK2MSFTNGP04.phx.gbl... [Quoted Text] > 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" <ed_millis[ at ]NO_SPAM.yahoo.com> wrote in message > news:eugtkd$4GHA.4832[ at ]TK2MSFTNGP06.phx.gbl... >> 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 >> > >

Re: Show which cell has MAX, MIN values? "Ed" <ed_millis[ at ]NO_SPAM.yahoo.com> 29.09.2006 20:01:19 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 [Quoted Text] > =ADDRESS(MATCH(N2999,N1:NN2998,FALSE),COLUMN(N1)) which I tried to copy well, but must have done something wrong?? Ed "Bernie Deitrick" <deitbe [ at ] consumer dot org> wrote in message news:%23ZnPai$4GHA.3512[ at ]TK2MSFTNGP04.phx.gbl... > 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" <ed_millis[ at ]NO_SPAM.yahoo.com> wrote in message > news:eugtkd$4GHA.4832[ at ]TK2MSFTNGP06.phx.gbl... >> 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 >> > >

Re: Show which cell has MAX, MIN values? "Bernie Deitrick" <deitbe [ at ] consumer dot org> 29.09.2006 20:09:51 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" <ed_millis[ at ]NO_SPAM.yahoo.com> wrote in message news:uwK4IIA5GHA.1460[ at ]TK2MSFTNGP05.phx.gbl... [Quoted Text] > 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" <deitbe [ at ] consumer dot org> wrote in message > news:%23ZnPai$4GHA.3512[ at ]TK2MSFTNGP04.phx.gbl... >> 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" <ed_millis[ at ]NO_SPAM.yahoo.com> wrote in message news:eugtkd$4GHA.4832[ at ]TK2MSFTNGP06.phx.gbl... >>> 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 >>> >> >> > >

Re: Show which cell has MAX, MIN values? "Ed" <ed_millis[ at ]NO_SPAM.yahoo.com> 29.09.2006 20:29:23 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 "Bernie Deitrick" <deitbe [ at ] consumer dot org> wrote in message news:O%23fFtLA5GHA.1900[ at ]TK2MSFTNGP02.phx.gbl... [Quoted Text] > 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" <ed_millis[ at ]NO_SPAM.yahoo.com> wrote in message > news:uwK4IIA5GHA.1460[ at ]TK2MSFTNGP05.phx.gbl... >> 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" <deitbe [ at ] consumer dot org> wrote in message >> news:%23ZnPai$4GHA.3512[ at ]TK2MSFTNGP04.phx.gbl... >>> 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" <ed_millis[ at ]NO_SPAM.yahoo.com> wrote in message >>> news:eugtkd$4GHA.4832[ at ]TK2MSFTNGP06.phx.gbl... >>>> 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 >>>> >>> >>> >> >> > >