+ Reply to Thread
Results 1 to 6 of 6

finding min and max value in a range of cells defined by a third cell

  1. #1
    Registered User
    Join Date
    10-15-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    19

    finding min and max value in a range of cells defined by a third cell

    Hello all

    I have found great help here in the past, so I'm trying my luck again...

    In Excel 2007 I have a table looking as follows:


    year name1 name2
    1985 0001 0002
    1990 0001 0002
    1991 0001 0002
    1997 0003 0004
    1998 0003 0004
    1999 0003 0004
    1950 0005 0006
    1955 0005 0006
    1980 0005 0006


    I would like to be able to output the min and max value of year (first and last year) for each pair of names, e.g. in a table like this:


    years name1 name2
    1985,1991 0001 0002
    1997,1999 0003 0004
    1950,1980 0005 0006


    or like this:


    year1 year2 name1 name2
    1985 1991 0001 0002
    1997 1999 0003 0004
    1950 1980 0005 0006


    Is there any formula/code to do so?

    If anyone can help, please do!

    Thanks in advance,
    N

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: finding min and max value in a range of cells defined by a third cell

    HI Narayan,

    Try using below formula for min:-

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    change min to max for maximum values, see attached:-
    finding min & max values.xlsx

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

  3. #3
    Registered User
    Join Date
    10-15-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: finding min and max value in a range of cells defined by a third cell

    Thanks for your quick reply, Dilipandey!

    Maybe I am not understanding it right (yet) or perhaps my question wasn't phrased well enough. In the file you sent, do I have to put in the values for cells F2 and G2 manually?
    This is what I would like to have Excel doing, actually. So for each combination of names in column B and C I would like to have Excel list the min and max value (for that pair of names, not for the entire column) of column A.

    Please let me know if your formula does that and I simply didn't understand it correctly...

    Thanks and kind regards,
    N

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: finding min and max value in a range of cells defined by a third cell

    In the file you sent, do I have to put in the values for cells F2 and G2 manually?
    Yes

    So for each combination of names in column B and C I would like to have Excel list the min and max value (for that pair of names, not for the entire column) of column A.
    Column B and C are not the names but the data which I considered to find the min and max values as per year.

    If you do not wish to enter anything apart from just providing the data source i.e., column A, B,C , then you could use pivot table.. but before that I need to understand your exact / final requirement ... please edit the workbook which I have uploaded and put in your thoughts and re-attach. Thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  5. #5
    Registered User
    Join Date
    10-15-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: finding min and max value in a range of cells defined by a third cell

    That was the keyword I needed... I am able to do it with a pivot table now. Great.
    I am evidentially lacking Excel routine

    Thanks for your help,
    N

  6. #6
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: finding min and max value in a range of cells defined by a third cell

    You are welcome Narayan..

    Glad that I could suggest you something better ... please mark this thread as [SOLVED].. thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1