+ Reply to Thread
Results 1 to 9 of 9

find max value based on criteria

  1. #1
    Forum Contributor
    Join Date
    06-29-2005
    Posts
    201

    find max value based on criteria

    Hi all

    I have tried to combine a vlookup function with a max function, without succes

    Let me explain
    I have 2 columns, 1 with descriptions, 1 with values

    On another place I have a summary of the descriptions. Next to this description I want to have the max value from the 2nd column described above

    (My demo makes hopefully more sence than my description)

    How can I get this result??

    Thanks in advance
    Hein
    Attached Files Attached Files

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016
    Posts
    3,359
    Hi,

    In your example, the following array will give the desired result

    =MAX(IF(C7:C12=G7,D7:D12,""))

    ....confirm this with CTRL, SHIFT and ENTER, excel will display it like this

    {=MAX(IF(C7:C12=G7,D7:D12,""))}


    =MAX(IF(range_of_labels=required_label,range_of_values,""))
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Forum Contributor
    Join Date
    06-29-2005
    Posts
    201
    Hi Dave
    Thanks for your input

    It works if there is only "apple".
    In my example I should have added more details, sorry for that

    What happens if in column D more fruit is added, eg pear, cherry etc, with their values in column D (there will be more than 1 pear etc)

    If I also add these fruits to column G, the max value stays the same, for every fruit it is the same value)

    How can I solve this

    Thanks Hein

    PS
    Once again sorry for not being complete

  4. #4
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561
    The formula given by Sweep works perfectly.

    Make sure the ranges in the formula are big enough and make sure you have entered it with Ctrl+shift+enter as described.

    Change G7 as appropriate.

    It would not be tricky to copy this formula further but if your real situation is more complex then I would recommend a pivot table.

    CC

  5. #5
    Forum Contributor
    Join Date
    06-29-2005
    Posts
    201
    Thanks all for your input
    The reason why it didn't work after the 1st try was that I forgot the ctrl-shft-enter combination!

    Last question (if permitted): what are the effects of this key-combination.
    In other words: when can I use this and why is it used?


    Thanks
    Hein

  6. #6
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016
    Posts
    3,359
    Hein,

    This is best explained by a true expert. Take a look here: http://www.cpearson.com/excel/ArrayFormulas.aspx

  7. #7
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561
    It creates what's called an array formula, effectively this tells Excel "where you expect to see one value in this formula, I have actually put a whole range, please perform the calculation on every item in the range"

    If you go to Tools-> formula auditing ->evaluate formula you will understand much more clearly. (try this on a small array size so you can understand what's going on)

    CC

  8. #8
    Forum Contributor
    Join Date
    06-29-2005
    Posts
    201
    Thanks Sweep and Charlie!!

    It has been a good learning experience for me!

    Hein

  9. #9
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561
    Glad it works Hein, and glad you've learned something. Please mark the thread as solved

    CC

+ 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