maximum negative value with if condition

[gomaa1239]

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

 

[63falcondude]

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

 

[JoeMo]

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]

Excel 2010
	A	B	C	D	E
1	columnA	columnB
2	-60	TRUE		Max Value	-1
3	-20	FALSE		Max Neg Value	-1
4	-30	FALSE
5	-1	TRUE
6	-7	TRUE
7	-15	FALSE
8	-4	TRUE
9	-5	FALSE
Sheet4

Cell Formulas
Range	Formula
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]

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]

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
	A	B	C
1	-60	TRUE	-60
2	-20	FALSE	*
3	-30	FALSE	*
4	-1	TRUE	*
5	-7	TRUE	*
6	-15	FALSE	*
7	-4	TRUE	*
8	-5	FALSE	*
Master

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