# maximum negative value with if condition

#### gomaa1239

##### New Member
Dears,

i need to get the maximum negative value of a range of a column based on a specific text in another coulmn
 columnA columnB -60 TRUE -20 FALSE -30 FALSE -1 TRUE -7 TRUE -15 FALSE -4 TRUE -5 FALSE

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>
i need to get the maximum negative value in columnA which is (-4) ignoring all values if i have false in coulmnB
this is the same thinking like =SUMIF(\$B\$2:\$B\$9,"false", A\$2:A\$9) but this is not working for maximum negative value

i found that we can get the maximum negative value using the following array formula =MIN(ABS(A2:A9))

Tanks a lot for your kind help

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### 63falcondude

##### Well-known Member
Wouldn't the maximum negative value (with TRUE in column B) be -1?

#### JoeMo

##### MrExcel MVP
Welcome to the forum!

By "maximum negative value" do you mean the most negative value ignoring all the values marked FALSE? If yes, why isn't that -60. If no, do you mean the least negative value? In that case why isn't that -1?

#### 63falcondude

##### Well-known Member

Excel 2010
ABCDE
1columnAcolumnB
2-60TRUEMax Value-1
3-20FALSEMax Neg Value-1
4-30FALSE
5-1TRUE
6-7TRUE
7-15FALSE
8-4TRUE
9-5FALSE
Sheet4
Cell Formulas
RangeFormula
E2{=MAX(IF(B2:B9=TRUE,A2:A9))}
E3{=MAX(IF((B2:B9=TRUE)*(A2:A9<0),A2:A9))}
Press CTRL+SHIFT+ENTER to enter array formulas.

#### gomaa1239

##### New Member
Welcome to the forum!

By "maximum negative value" do you mean the most negative value ignoring all the values marked FALSE? If yes, why isn't that -60. If no, do you mean the least negative value? In that case why isn't that -1?
sorry-i-need-the-minimum-negative-value

#### JoeMo

##### MrExcel MVP
sorry-i-need-the-minimum-negative-value
You didn't answer the questions. If you want the most-negative value, ignoring False, try this - enter using ctrl+shift+enter.
Excel Workbook
ABC
1-60TRUE-60
2-20FALSE*
3-30FALSE*
4-1TRUE*
5-7TRUE*
6-15FALSE*
7-4TRUE*
8-5FALSE*
Master

If you want the least-negative value try the formula in E3 from post #4.

Replies
3
Views
803
Replies
4
Views
2K
Replies
0
Views
619
Replies
27
Views
1K
Replies
0
Views
1K

1,191,191
Messages
5,985,216
Members
439,948
Latest member
Xearo96

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back