If statement with multiple AND/OR criteria

[aleonard]

A	B	C	D
10,000	400	23,000	2,498
50,000	-1325	26,000	57
20,000	505	37,000	-104
30,000	-8,000	21,000	-1,035
17,000	6,032	14,000	2,045

<tbody>
</tbody>

Hi All,

How can I write an If statement that scans all of Column B and Column D looking for any results that are BOTH less than -4,000 AND less than -4% of the corresponding value immediately to its left? (Example: the 4th value in the B column would be the only result that would satisfy both criteria, because -8,000 is less than -4,000 AND -8,000 is less than -4% of 30,000.

Thanks so much for the help - this forum is incredible.

-A

 

[njimack]

You haven't said how you want the results displayed - the easiest method would be via conditional formatting...

Excel Workbook
	A	B	C	D
1	10,000	400	23,000	2498
2	50,000	-1325	26,000	57
3	20,000	505	37,000	-104
4	30,000	-8,000	21,000	-1,035
5	17,000	6,032	14,000	2,045
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1	1. / Formula is =AND(B1<-4000,B1<(A1*-4%))	Abc
D1	1. / Formula is =AND(D1<-4000,D1<(C1*-4%))	Abc
B2	1. / Formula is =AND(B1<-4000,B1<(A1*-4%))	Abc
D2	1. / Formula is =AND(D1<-4000,D1<(C1*-4%))	Abc
B3	1. / Formula is =AND(B1<-4000,B1<(A1*-4%))	Abc
D3	1. / Formula is =AND(D1<-4000,D1<(C1*-4%))	Abc
B4	1. / Formula is =AND(B1<-4000,B1<(A1*-4%))	Abc
D4	1. / Formula is =AND(D1<-4000,D1<(C1*-4%))	Abc
B5	1. / Formula is =AND(B1<-4000,B1<(A1*-4%))	Abc
D5	1. / Formula is =AND(D1<-4000,D1<(C1*-4%))	Abc

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​						-4000​	value(s)
2​	10,000​	400​	23,000​	2,498​		4%​	-8000​
3​	50,000​	-1325​	26,000​	57​		count
4​	20,000​	505​	37,000​	-104​		1​
5​	30,000​	-8,000​	21,000​	-1,035​
6​	17,000​	6,032​	14,000​	2,045​

F4, control+shift+enter (cse), not just enter:

=SUM(IF(MOD(COLUMN(B2:D6)-COLUMN(B2),2)=0,
    IF(B2:D6<F1,IF(B2:D6<F2*A2:C6,1))))

G2, cse and copy down:

=IF(ROWS($G$2:G2)<=$F$4,
   SMALL(IF(MOD(COLUMN($B$2:$D$6)-COLUMN($B$2),2)=0,
   IF($B$2:$D$6<$F$1,IF($B$2:$D$6<$F$2*$A$2:$C$6,$B$2:$D$6))),
   ROWS($G$2:G2)),"")

 

[aleonard]

Sorry, I should have clarified. The output should just be a cell that says YES if any cells in Columns B or D match both of these criteria, and a NO if none do.

Thanks so much,
A

 

[Cosmic Wizard]

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​						-4000​	value(s)
2​	10,000​	400​	23,000​	2,498​		4%​	-8000​
3​	50,000​	-1325​	26,000​	57​		count
4​	20,000​	505​	37,000​	-104​		1​
5​	30,000​	-8,000​	21,000​	-1,035​
6​	17,000​	6,032​	14,000​	2,045​

<tbody>
</tbody>

F4, control+shift+enter (cse), not just enter:

=SUM(IF(MOD(COLUMN(B2:D6)-COLUMN(B2),2)=0,
    IF(B2:D6<f1,if(b2:d6<f2*a2:c6,1))))
</f1,if(b2:d6<f2*a2:c6,1))))

G2, cse and copy down:

=IF(ROWS($G$2:G2)<=$F$4,
   SMALL(IF(MOD(COLUMN($B$2:$D$6)-COLUMN($B$2),2)=0,
   IF($B$2:$D$6<$F$1,IF($B$2:$D$6<$F$2*$A$2:$C$6,$B$2:$D$6))),
   ROWS($G$2:G2)),"")

Hi Aladin, your first formula I can't seem to get to work: is it missing more than a ) ??
Is the count heading meant to be next to the value heading?
Sorry if I am a bit confused, always love all your work!

 

[Aladin Akyurek]

Hi Aladin, your first formula I can't seem to get to work: is it missing more than a ) ??
Is the count heading meant to be next to the value heading?
Sorry if I am a bit confused, always love all your work!

Vagaries of html...

F4, control+shift-enter:

=SUM(IF(MOD(COLUMN(B2:D6)-COLUMN(B2),2)=0,
   IF(B2:D6 < F1,IF(B2:D6 < F2*A2:C6,1))))

G2, control+shift+enter and copy down:

=IF(ROWS($G$2:G2)<=$F$4,
   SMALL(IF(MOD(COLUMN($B$2:$D$6)-COLUMN($B$2),2)=0,
   IF($B$2:$D$6 < $F$1,IF($B$2:$D$6 < $F$2*$A$2:$C$6,$B$2:$D$6))),
   ROWS($G$2:G2)),"")

To see why we need such a formula, change the value of D6 to -9,000.

 

[Cosmic Wizard]

Thankyou!!

 

[Aladin Akyurek]

Thankyou!!

You are welcome.