Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    May 2002
    Location
    California
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MinIf/MaxIf (All)

    Does anyone know of a formula to calculate the min or max of a range subject to a condition, akin to the SumIf and CountIf funtions? I've tried array formulae, but as far as I've been able to tell, the condition must be a constant, not a cell reference. My particular situation, which I would guess is common enough, is a row or column of monthly data for multiple years; for each year, I'd like to find the largest or smallest value.

    I've written a VBA function to perform the maxif function, but I can't use it in every situation.

    Eric

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: MinIf/MaxIf (All)

    An array formula will work (but it ain't pretty <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)

    I have attached a spreadsheet with hourly values (power production in Alberta if you are really curious) that I have extracted from a s/sheet that I was using for something else. Down at the bottom of the range I have put in array formulas to calculate the minimum and maximum values encountered in each day.

    The source data is laid out with the day in column A, the hour in column B, and the production in Column C, in the range from Row 2 through 155. In column A, I have put in the days that I want to use as my criteria, from row 158 through 163.

    The formula for the maximum production on the first day (see cell C158) is:
    <pre>=MAX(IF($A$2:$A$155=$A158,$C$2:$C$155,MIN($C$ 2:$C$155)))
    </pre>

    Which must be entered as an array formula (ctrl-shift-enter)

    The formula examines all the dates in the data range, and compares them to the date in the criteria cell (in this case A158). If the values match, the If function returns the value in the production cell (Column C) for the particular day; if they don't match it returns the minimum value observed throughout the period (this it to avoid guessing what might be the highest and lowest values). The max function then identifies the highest returned value - which MUST be from a day that matches the criteria. The minimum formula (see cell D158) is analogous, with MAX and MIN swapped around. The absolute and relative references are just to make it easy to drag the formula to copy without losing the references.

    Hope that helps
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    May 2002
    Location
    California
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MinIf/MaxIf (All) - Thanks

    Works like a charm. I tried to do something similar, but obviously got something wrong (easy to do with array formulae).

    Thanks much. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: MinIf/MaxIf (All)

    Dean,

    I'm a little confused as to the need for having the max as part of the "minif" array formula to avoid the guessing. I'm assuming this is to provide something where a match is not made - kind of as you seem to suggest.

    What I do is just use "xx" for the false part of the if test. Min and Max will ignore non-numeric values so any text values would work. So my array formula would be
    for MINIF: =min(if(a1:a5=d1,b1:b5,"xx")) array-entered

    Fred

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: MinIf/MaxIf (All)

    Fred - you are right. I had first thought of doing the "maxif" array formula and returning zero for the cells where the criteria range didn't match the specification - and then I thought "what about a range of negative numbers, where zero would be the maximum. Returning the absolute minimum avoids the problem - but I didn't even think of using a non-numeric value, which also avoids the problem.

    My guess is that your version will execute (slightly) faster, since excel isn't evaluating the minimum of the entire range on each 'loop' through the array - although I do not know how much it slows down the Max or Min function to disregard a string value - my guess is not very much...

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MinIf/MaxIf (All)

    How about just

    {=MAX(($A$2:$A$155=$A158)*($C$2:$C$155))}

    instead of:

    {=MAX(IF($A$2:$A$155=$A158,$C$2:$C$155,MIN($C$2:$C $155)))}

    And

    {=MIN(IF(($A$2:$A$155=$A158)*($C$2:$C$155),$C$2:$C $155))}

    instead of either:

    {=MIN(IF($A$2:$A$155=$A158,$C$2:$C$155,MAX($C$2:$C $155)))}

    or, Fred's suggestion,

    {=MIN(IF($A$2:$A$155=$A158,$C$2:$C$155,"xx"))} ?

    It's probably worth assessing which of the MIN/IF formulas would execute faster.

    Aladin
    Microsoft MVP - Excel

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MinIf/MaxIf (All)

    Try using Pivot Tables.
    They allow use of min /max functionality and a lot more.
    It may be easier that manipulating the fomulas for speed.

Posting Permissions

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