Count conditional, only starting and max

[ibmy]

Hi, need expert help me on this study data.
Need 5 different result.


(1) The count start when Column B and Column C are positve value ( > 0 ).

The Result in Column F only show starting number (number 1) and max value.

eu 5.6.xlsb
	A	B	C	D	E	F
1		B	C			Result
2
3		0.1	0.1			1
4		0	0.2
5		0	0
6		0.1	0.3			1
7		0.1	0.5
8		0.2	0.5			3
9		0.3	0
10		0.4	0.4			1
11		0.1	-0.3
12		0.1	0.3			1
13		0.1	0.6			2
14		-0.4	-0.3
15		-0.2	0.5
16		0.1	0.1			1
17		0.1	0.3
18		0.1	0.1			3
19		-0.4	0
Sheet5

(2) Same from point (1) above, the only difference is Column B and C are negative value ( < 0 )

eu 5.6.xlsb
	A	B	C	D	E	F
1		B	C			Result
2
3		-0.1	-0.1			1
4		0	-0.2
5		0	0
6		-0.1	-0.3			1
7		-0.1	-0.5
8		-0.2	-0.5			3
9		-0.3	0
10		-0.4	-0.4			1
11		-0.1	0.3
12		-0.1	-0.3			1
13		-0.1	-0.6			2
14		0.4	0.3
15		0.2	-0.5
16		-0.1	-0.1			1
17		-0.1	-0.3
18		-0.1	-0.1			3
19		0.4	0
Sheet6

(3) The count start when Column B and Column C are positive value( > 0 ) AND next row of value of Column D is same value from previous row in Column D

eu 5.6.xlsb
	A	B	C	D	E	F
1		B	C	D		Result
2
3		0.1	0.1	1.4
4		0	0.2	1.3
5		0	0	1.3
6		0.1	0.3	1.3		1
7		0.1	0.5	1.3
8		0.2	0.5	1.3		3
9		0.3	0	1.3
10		0.4	0.4	1.2
11		0.1	-0.3	1.5
12		0.1	0.3	1.5		1
13		0.1	0.6	1.5		2
14		-0.4	-0.3	1.5
15		-0.2	0.5	1.5
16		0.1	0.1	1.4
17		0.1	0.3	1.7		1
18		0.1	0.1	1.7		2
19		-0.4	0	1.7
Sheet7

(4) Same from point (3) above, the only difference is Column B and C are negative value ( < 0 )

eu 5.6.xlsb
	A	B	C	D	E	F
1		B	C	D		Result
2
3		-0.1	-0.1	1.4
4		0	-0.2	1.3
5		0	0	1.3
6		-0.1	-0.3	1.3		1
7		-0.1	-0.5	1.3
8		-0.2	-0.5	1.3		3
9		-0.3	0	1.3
10		-0.4	-0.4	1.2
11		-0.1	0.3	1.5
12		-0.1	-0.3	1.5		1
13		-0.1	-0.6	1.5		2
14		0.4	0.3	1.5
15		0.2	-0.5	1.5
16		-0.1	-0.1	1.4
17		-0.1	-0.3	1.7		1
18		-0.1	-0.1	1.7		2
19		0.4	0	1.7
Sheet8

(5) This one, I would like the count start when value of Column D at 'specific number' and Column B and Column C are postive value or negative value. (same like point 1 and 2 above).

i) Value at Column D is 1.3 with Column B and Column C are positive value

eu 5.6.xlsb
	A	B	C	D	E	F
1		B	C	D		Result
2
3		0.1	0.1	1.4
4		0	0.2	1.3
5		0	0	1.3
6		0.1	0.3	1.3		1
7		0.1	0.5	1.3
8		0.2	0.5	1.3		3
9		0.3	0	1.3
10		0.4	0.4	1.5
11		0.1	-0.3	1.5
12		0.1	0.3	1.3		1
13		0.1	0.6	1.3		2
14		-0.4	-0.3	1.5
15		-0.2	0.5	1.2
16		0.1	0.1	1.2
17		0.1	0.3	1.3		1
18		0.1	0.1	1.3		2
19		-0.4	0	1.7
Sheet9

ii) Value at Column D is 1.1 with Column B and Column C are negative value

eu 5.6.xlsb
	A	B	C	D	E	F
1		B	C	D		Result
2
3		0.1	0.1	1.4
4		0	0.2	1.3
5		0	0	1.3
6		-0.1	-0.3	1.1		1
7		-0.1	-0.5	1.1
8		-0.2	-0.5	1.1		3
9		0.3	0	1.3
10		0.4	0.4	1.5
11		0.1	-0.3	1.5
12		-0.1	-0.3	1.1		1
13		-0.1	-0.6	1.1		2
14		-0.4	-0.3	1.5
15		-0.2	0.5	1.2
16		0.1	0.1	1.2
17		-0.1	-0.3	1.1		1
18		-0.1	-0.1	1.1		2
19		-0.4	0	1.7
Sheet10

iii) 1.7, 1.9, 2.0, etc ...
> Let me know what do I need to change in formula for specific value in Column D with Column B and Column C are postive or negative value


Side Note:
1. The lowest positive value in Column B and Column C is 0.1
2. The lowest negative value in Column B and Column C is -0.1
3. The value in Column D always positve.

p/s : My data about 60k-250k row
: sorry for bad English

Thanks, appreaciate all your help.

 

[jasonb75]

Asking for 6 things in one post is not a good idea, it can get very messy.

Thiss looks like it just needs some variations to the formula that I provided in your last thread. For the first one, try

=IF(AND(B3>0,C3>0,OR(B2<=0,C2<=0)),1,IF(AND(C3>0,B3>0,OR(C4<=0,B4<=0)),ROW(F3)-IFERROR(MATCH(1E+100,F$2:F2),0),""))

For the second one, use the same formula but change > to < and < to >

I'm not even going to look at the others until you test these and confirm that they are working correctly.

 

[ibmy]

Thanks @jasonb75 it works great

What I did before to achieve for the first one and second one is a long way but the formula u gave save me a lot of time.

This is what I did before :

(1) & (2) First objective and second objective actually to get result in broad.

1. Column H -> To get true value (1) for postive number/negative number for both Column B and Column C:

=if(and(B3>0,C3>0)1,0) / =if(and(B3<0,C3<0)1,0)

2. Column I -> Then I apply the formula you provided in previous thread Starting (1) & Max Only , thanks to you again

3. Then I apply filter to see all available Max which interest me.

(3) & (4) Third objective and fourth objective is to get the result in narrow

1. Same step as above -> =if(and(B3>0,C3>0)1,0) / =if(and(B3<0,C3<0)1,0)
2. Same step as above -> Starting (1) & Max Only
3. This is the problem come in. After apply filter, I manually check for every single max value and find it some of the row in Column D is not same as previous its. It took a lot of time here.

(5) Fifth objective to get the result more narrow

1. Same as step above -> =if(and(B3>0,C3>0)1,0) / =if(and(B3<0,C3<0)1,0)
2. Column I -> =if(D=1.3,1,0) // depend on what value D I interested in
3. After that I use =if(and()) function to get result for both step 1 and 2 true
4. Finally I use formula Starting (1) & Max Only
5. Apply filter to see all available Max.

p/s: As you can see, Im only know how to use basic formula like ( =if or =if(and) function then make it multiple to get true result, unlike formula given by expert here in forum which is mutiple things in single formula.

: If u have free time, would you help me for objective 3,4 and 5. Not forcing you. Really appreaciate your help.

 

[jasonb75]

Please check your expected results for the third example (sheet7) in post 1 and either confirm that they are correct, or post the correct results.

Reading the question D17 is not equal to D16, so does not meet the criteria and should not be counted.

 

[ibmy]

Update : Sorry for wrong exptected result for (3) and (4) in post 1, I read it a wrong way

(3) The count start when current Column B and Column C are positive value ( > 0 ) AND current Column D value is same as previous Column D value with previous Column B and C are positive value ( > 0 )

eu 5.6.xlsb
	A	B	C	D	E	F
1		B	C	D		Result
2
3		0.1	0.1	1.4
4		0	0.2	1.3
5		0	0	1.3
6		0.1	0.3	1.3
7		0.1	0.5	1.3		1
8		0.2	0.5	1.3
9		0.3	0.3	1.3		3
10		0.4	0.4	1.2
11		0.1	-0.3	1.5
12		0.1	0.3	1.5
13		0.1	0.6	1.5		1
14		-0.4	-0.3	1.5
15		-0.2	0.5	1.5
16		0.1	0.1	1.4
17		0.1	0.3	1.7
18		0.1	0.1	1.7		1
19		0.2	0.2	1.7		2
20		0	0.5	1.7
Sheet7

(4) The count start when current Column B and Column C are negative value (< 0 ) AND current Column D value is same as previous Column D value with previous Column B and C are negative value ( < 0 )

eu 5.6.xlsb
	A	B	C	D	E	F
1		B	C	D		Result
2
3		-0.1	-0.1	1.4
4		0	-0.2	1.3
5		0	0	1.3
6		-0.1	-0.3	1.3
7		-0.1	-0.5	1.3		1
8		-0.2	-0.5	1.3
9		-0.3	-0.3	1.3		3
10		-0.4	-0.4	1.2
11		-0.1	0.3	1.5
12		-0.1	-0.3	1.5
13		-0.1	-0.6	1.5		1
14		0.4	0.3	1.5
15		0.2	-0.5	1.5
16		-0.1	-0.1	1.4
17		-0.1	-0.3	1.7
18		-0.1	-0.1	1.7		1
19		-0.2	-0.2	1.7		2
20		0	-0.5	1.7
Sheet8

 

[jasonb75]

When I questioned your results, I thought that only the last one was wrong. With your new example, there is too much conflicting logic, I do not think that it will be possible to produce those results with a formula.

 

[ibmy]

Thanks @jasonb75 , you help me a lot already.

Its ok for objective (3) & (4) cannot be formulated . Objective (5) can do objective (3) & (4) job precisely / more narrow result.

Can you help me on objective (5) when you have free time. Thanks

 

[jasonb75]

Can you help me on objective (5) when you have free time.

That one is a bit easier, this formula goes into F3 based on your examples, with the 'Specific number' to look for in column D located in $H$2.

=IF(AND(B3>0,C3>0,D3=$H$2,OR(B2<=0,C2<=0,D2<>$H$2)),1,IF(AND(C3>0,B3>0,D3=$H$2,OR(C4<=0,B4<=0,D4<>$H$2)),ROW(F3)-IFERROR(MATCH(1E+100,F$2:F2),0),""))

As with the formula for parts 1 and 2, this one is for positive values, switching the positions of < and > will make it work for negative values (don't change the ones for column D).

 

[ibmy]

Hi @jasonb75 and all helpers

Can you help me on modify formula in Post #2 . I would like the result show count in order/sequence (like usual count).

Current formula it shows :
1-3
1-6
1-8

Expected result from new modifiy formula :
1,2,3
1,2,3,4,5,6
1,2,3,4,5,6,7,8

 

[jasonb75]

This one for positive values, same changes as before if you need negative as well.

=IF(AND(B3>0,C3>0),N(F2)+1,"")