Find max or min in excel, with conditions

Question

In my table, I came across negative and positive values. I need to get a minimum among all positive values ​​and a maximum among all negative ones. How can i do this?

Answer 1

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

Maximum minus:

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

Press Ctrl + Shift + Enter while entering a formula. Then the formula appears in brackets with {...}.

Minimum positive:

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

Also enter the array formula by pressing Ctrl + Shift + Enter