CODEWITHSUNDEEP
excelconditional-statementsmaxminimum
Ricky Robinson
Ricky Robinson

Reputation: 22943

find max or min in excel, with conditions

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?

Upvotes: 4

Views: 25974

Answers (1)

Excellll
Excellll

Reputation: 5785

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

Upvotes: 7

Related Questions