MinIf/MaxIf (All)

[ehsu]

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

[dcardno]

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

[ehsu]

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>

[fburg]

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

[dcardno]

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...

[Aladin Akyurek]

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

[kieran]

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.