Results 1 to 7 of 7
Thread: MinIf/MaxIf (All)

20020712, 21:32 #1
 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

20020712, 23:04 #2
 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 (ctrlshiftenter)
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

20020713, 00:17 #3
 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>

20020714, 19:19 #4
 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 nonnumeric values so any text values would work. So my array formula would be
for MINIF: =min(if(a1:a5=d1,b1:b5,"xx")) arrayentered
Fred

20020714, 21:13 #5
 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 nonnumeric 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...

20020715, 07:06 #6
 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.
AladinMicrosoft MVP  Excel

20020716, 00:14 #7
 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.