find max or min in excel, with conditions

excel conditional max minimum
Ricky Robinson picture Ricky Robinson · Jul 12, 2011 · Viewed 25.6k times · Source

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?

Answer

Excellll picture Excellll · Jul 12, 2011

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

Related questions

(Excel) Conditional Formatting based on Adjacent Cell Value

I'm trying to apply conditional formatting in Excel on a range of cells, based on the adjacent cell's value, to achieve something like this: The goal is to highlight values in Column B (Actual Expense) red if the value is …

Read More
Using SUMIFS with multiple AND OR conditions

I would like to create a succinct Excel formula that SUMS a column based on a set of AND conditions, plus a set of OR conditions. My Excel table contains the following data and I used defined names for the …

Read More
Excel - Conditional Formatting - Cell not blank and equals 0

I'm trying to check if a cell is: blank, and if false (not blank) If value equals 0. I have tried: =IF( NOT( ISBLANK($D:$D) ) & $D:$D=0 ,TRUE,FALSE) =IF( AND( NOT( ISBLANK($D:$D) ),$D:$D=0) ,TRUE,FALSE) …

Read More