Conditional Min/Max

[davidepstein22]

I use the following formula in my spreadsheet and it works (thanks for the example Mr. Excel!!).

=MIN(IF(('PO List'!S:S="In Approval")*('PO List'!A:A=C2),'PO List'!L:L))

Does the "*" (asterisk) act as a true multiplier or is it doing something special? I interpret the expression to mean if the first condition is true and the second condition is true then return the corresponding value. If my memory is correct, a true condition = -1; so, multiplying -1 * -1 = +1 (not -1). Hence, I don't understand why the formula is working.

Second question - Is the reason that I cannot use a If(and( construct because Excel only allows the user to create a single dimension array? If this is not true, then why isthe contruct not acceptable?


Thank you,
Dave

 

[barry houdini]

Hello Dave, welcome to MrEXcel

No, TRUE becomes 1 when coerced here so 1*1 = 1, clearly. I believe that in VBA TRUE = -1

You can't use AND in these type of formulas because AND returns a single result rather than the array that is required

 

[T. Valko]

I use the following formula in my spreadsheet and it works (thanks for the example Mr. Excel!!).

=MIN(IF(('PO List'!S:S="In Approval")*('PO List'!A:A=C2),'PO List'!L:L))

Does the "*" (asterisk) act as a true multiplier or is it doing something special? I interpret the expression to mean if the first condition is true and the second condition is true then return the corresponding value. If my memory is correct, a true condition = -1; so, multiplying -1 * -1 = +1 (not -1). Hence, I don't understand why the formula is working.

Second question - Is the reason that I cannot use a If(and( construct because Excel only allows the user to create a single dimension array? If this is not true, then why isthe contruct not acceptable?


Thank you,
Dave

Barry has answered your question about array multiplication.

A couple of additional points...

You should avoid using entire columns as range references in array formulas (unless you are of course using every cell in the column to hold data). An array formula will evaluate EVERY cell referenced and in Excel 2007 and later a full column contains over 1 million cells. So, if you have data down to row 1000 then you're being very inefficient by having to evaluate all those empty unused cells.

A slightly more efficient method is to use nested IFs rather than array multiplication. Like this:

=MIN(IF('PO List'!S1:S1000="In Approval",IF('PO List'!A1:A1000=C2,'PO List'!L1:L1000)))