MAX and MIN within array formulas

[tlm]

The following formula will evaluate correctly using the
MAX function, but not the MIN function. What is the
reason?

{=MIN(IF($A$6:$A$12="Plant1",IF
($E$6:$E$12="A",$F$6:$F$12,0),0))}

 

[Bernie Deitrick]

tlm,

Because you are returning 0's when the condition is false, so you are
probably getting 0 as your result.

Instead of 0, use MAX of the range of interest. Array enter:

=MIN(IF($A$6:$A$12="Plant1",IF($E$6:$E$12="A",$F$6:$F$12,MAX($F$6:$F$12),MAX
($F$6:$F$12)))

HTH,
Bernie
MS Excel MVP

 

[Don Guillett]

try it this way. modify to suit. Don't think you will need to array enter.

=MIN(IF(($A$3:$A$43="plant1")*($E$3:$E$43="a"),$F$3:$F$43))

 

[Don Guillett]

You WILL have to array enter

 

[Myrna Larson]

You have said to return 0 if the comparisons are false. Assuming there are no
negative values in F6:F12, and there are some non-matches in columns A and E,
the minimum will always be 0.

 

[Myrna Larson]

I guess I forgot to tell you how to fix this: just omit the 0's, i.e.

{=MIN(IF($A$6:$A$12="Plant1",IF($E$6:$E$12="A",$F$6:$F$12)))}

which is equivalent to

{=MIN(IF($A$6:$A$12="Plant1",IF($E$6:$E$12="A",$F$6:$F$12,FALSE),FALSE))}

If the comparison is FALSE, the result is the Boolean value FALSE; MAX and MIN
ignore TRUE and FALSE and look only at the numbers in the range.