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?
4
- Possible duplicate of Excel: Find min/max values in a column among those matched from another columnsancho.s ReinstateMonicaCellio– sancho.s ReinstateMonicaCellio2017-02-04 12:01:05 +00:00Commented Feb 4, 2017 at 12:01
- How exactly can a question from 2011 with an answer given in 2011 be a duplicate of a question asked in 2015?Ricky Robinson– Ricky Robinson2017-02-06 09:29:40 +00:00Commented Feb 6, 2017 at 9:29
- 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.sancho.s ReinstateMonicaCellio– sancho.s ReinstateMonicaCellio2017-02-06 10:44:36 +00:00Commented Feb 6, 2017 at 10:44
- Oh all right, I see now.Ricky Robinson– Ricky Robinson2017-02-06 16:03:41 +00:00Commented Feb 6, 2017 at 16:03
Add a comment |
1 Answer
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
1 Comment
sam2426679
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