+ Reply to Thread
Results 1 to 6 of 6

Maximum and Minimum with Complex Criteria

  1. #1

    Maximum and Minimum with Complex Criteria

    Hello


    I hope i am not duplicating any previous query.


    I am trying to generate a minimum and maximum summary for a list that
    includes similar logical labels.


    For example


    If my list is


    Community Park.....10ha
    District Park...20ha
    Canal..3ha
    Open Space..4ha
    Lake...1ha


    I want to find the min/ max of all types of Green spaces (Community
    Park, District Park and Open Space) and Water Bodies (Lake, Canal).


    Is there an easy way to do this without adding another column?


    For example one could add a col that identifies Community Park,
    District Park and Open Space as Green Spaces and search the min/ max in

    this but as my list is long and needs frequent updates it would be a
    pain to maintain.


    Thanks in advance.


    Manosh


  2. #2
    Bob Phillips
    Guest

    Re: Maximum and Minimum with Complex Criteria

    A few assumptions made, but try

    =MAX(IF(ISNUMBER(FIND({"Park","Space"},A1:A10)),C1:C10))

    as an array formula, so commit with Ctrl-Shift-Enter

    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    <[email protected]> wrote in message
    news:[email protected]...
    > Hello
    >
    >
    > I hope i am not duplicating any previous query.
    >
    >
    > I am trying to generate a minimum and maximum summary for a list that
    > includes similar logical labels.
    >
    >
    > For example
    >
    >
    > If my list is
    >
    >
    > Community Park.....10ha
    > District Park...20ha
    > Canal..3ha
    > Open Space..4ha
    > Lake...1ha
    >
    >
    > I want to find the min/ max of all types of Green spaces (Community
    > Park, District Park and Open Space) and Water Bodies (Lake, Canal).
    >
    >
    > Is there an easy way to do this without adding another column?
    >
    >
    > For example one could add a col that identifies Community Park,
    > District Park and Open Space as Green Spaces and search the min/ max in
    >
    > this but as my list is long and needs frequent updates it would be a
    > pain to maintain.
    >
    >
    > Thanks in advance.
    >
    >
    > Manosh
    >




  3. #3
    Manosh
    Guest

    Re: Maximum and Minimum with Complex Criteria

    What would the formula for minimum be?
    I tried to replace Max wih min but it doesnt work.
    Would appreciate an answer on this,
    regards
    m


  4. #4
    Bob Phillips
    Guest

    Re: Maximum and Minimum with Complex Criteria

    It worked for me.

    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    "Manosh" <[email protected]> wrote in message
    news:[email protected]...
    > What would the formula for minimum be?
    > I tried to replace Max wih min but it doesnt work.
    > Would appreciate an answer on this,
    > regards
    > m
    >




  5. #5
    Bernie Deitrick
    Guest

    Re: Maximum and Minimum with Complex Criteria

    =MIN(IF(ISNUMBER(FIND({"Park","Space"},A1:A10)),C1:C10,MAX(C1:C10)))

    Also entered using Ctrl-Shift-Enter.

    HTH,
    Bernie
    MS Excel MVP


    "Manosh" <[email protected]> wrote in message
    news:[email protected]...
    > What would the formula for minimum be?
    > I tried to replace Max wih min but it doesnt work.
    > Would appreciate an answer on this,
    > regards
    > m
    >




  6. #6
    Harlan Grove
    Guest

    Re: Maximum and Minimum with Complex Criteria

    "Bernie Deitrick" <deitbe @ consumer dot org> wrote...
    >=MIN(IF(ISNUMBER(FIND({"Park","Space"},A1:A10)),C1:C10,MAX(C1:C10)))
    >
    >Also entered using Ctrl-Shift-Enter.

    ....

    Or

    =-MAX(IF(ISNUMBER(FIND({"Park","Space"},A1:A10)),-C1:C10))



+ 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