find max or min in excel, with conditions

Question

In my spreadsheet I have a column with negative and positive values. I need to get the minimum among all positive values and the maximum among all negative ones. How can I do so?

Mullis

Possible duplicate of Excel: Find min/max values in a column among those matched from another column – Pampuch

How exactly can a question from 2011 with an answer given in 2011 be a duplicate of a question asked in 2015? – Mullis

As per many other threads (I will later look for them) a "duplicate" in SO/SE does not require a later date than the "original"; it might be a misnomer. The criterion is that the "original" would have more answers, and/or more "quality", as per the upvotes of the question and answers. Such a measure is not univocally specified, although a formula could be put together. And it's not that I agree with a criterion like that (I see pros and cons for that), but only that I have seen quite a few people supporting it. The point is that people finding this OP get to the answers posted in the other. – Pampuch

Oh all right, I see now. – Mullis

Answer 1

Use array formulas. In the following examples, the values you're checking are in A2:A10.

Maximum negative:

=MAX(IF(A2:A10<0,A2:A10))

Press Ctrl+Shift+Enter when entering the formula. Formula will then appear bracketed by {...}.

Minimum positive:

=MIN(IF(A2:A10>=0,A2:A10))

Also enter as an array formula by pressing Ctrl+Shift+Enter

Ton

You should note that there is a corner case where this formula will return misinformation; in the case where the IF condition does not match anything (0 records meet the criteria), it will return 0 instead of what should be #N/A, e.g. =MIN( IF(FALSE, 99) ) == 0 – Anticathexis