Count conditional, only starting and max

ibmy

Board Regular
Joined
Mar 4, 2020
Messages
51
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEF
1BCResult
2
30.10.11
400.2
500
60.10.31
70.10.5
80.20.53
90.30
100.40.41
110.1-0.3
120.10.31
130.10.62
14-0.4-0.3
15-0.20.5
160.10.11
170.10.3
180.10.13
19-0.40
Sheet5

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

eu 5.6.xlsb
ABCDEF
1BCResult
2
3-0.1-0.11
40-0.2
500
6-0.1-0.31
7-0.1-0.5
8-0.2-0.53
9-0.30
10-0.4-0.41
11-0.10.3
12-0.1-0.31
13-0.1-0.62
140.40.3
150.2-0.5
16-0.1-0.11
17-0.1-0.3
18-0.1-0.13
190.40
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
ABCDEF
1BCDResult
2
30.10.11.4
400.21.3
5001.3
60.10.31.31
70.10.51.3
80.20.51.33
90.301.3
100.40.41.2
110.1-0.31.5
120.10.31.51
130.10.61.52
14-0.4-0.31.5
15-0.20.51.5
160.10.11.4
170.10.31.71
180.10.11.72
19-0.401.7
Sheet7

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

eu 5.6.xlsb
ABCDEF
1BCDResult
2
3-0.1-0.11.4
40-0.21.3
5001.3
6-0.1-0.31.31
7-0.1-0.51.3
8-0.2-0.51.33
9-0.301.3
10-0.4-0.41.2
11-0.10.31.5
12-0.1-0.31.51
13-0.1-0.61.52
140.40.31.5
150.2-0.51.5
16-0.1-0.11.4
17-0.1-0.31.71
18-0.1-0.11.72
190.401.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
ABCDEF
1BCDResult
2
30.10.11.4
400.21.3
5001.3
60.10.31.31
70.10.51.3
80.20.51.33
90.301.3
100.40.41.5
110.1-0.31.5
120.10.31.31
130.10.61.32
14-0.4-0.31.5
15-0.20.51.2
160.10.11.2
170.10.31.31
180.10.11.32
19-0.401.7
Sheet9

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

eu 5.6.xlsb
ABCDEF
1BCDResult
2
30.10.11.4
400.21.3
5001.3
6-0.1-0.31.11
7-0.1-0.51.1
8-0.2-0.51.13
90.301.3
100.40.41.5
110.1-0.31.5
12-0.1-0.31.11
13-0.1-0.61.12
14-0.4-0.31.5
15-0.20.51.2
160.10.11.2
17-0.1-0.31.11
18-0.1-0.11.12
19-0.401.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.
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,399
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Mar 4, 2020
Messages
51
Office Version
  1. 2016
Platform
  1. Windows
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 (y)

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

Well-known Member
Joined
Dec 30, 2008
Messages
12,399
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Mar 4, 2020
Messages
51
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
ABCDEF
1BCDResult
2
30.10.11.4
400.21.3
5001.3
60.10.31.3
70.10.51.31
80.20.51.3
90.30.31.33
100.40.41.2
110.1-0.31.5
120.10.31.5
130.10.61.51
14-0.4-0.31.5
15-0.20.51.5
160.10.11.4
170.10.31.7
180.10.11.71
190.20.21.72
2000.51.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
ABCDEF
1BCDResult
2
3-0.1-0.11.4
40-0.21.3
5001.3
6-0.1-0.31.3
7-0.1-0.51.31
8-0.2-0.51.3
9-0.3-0.31.33
10-0.4-0.41.2
11-0.10.31.5
12-0.1-0.31.5
13-0.1-0.61.51
140.40.31.5
150.2-0.51.5
16-0.1-0.11.4
17-0.1-0.31.7
18-0.1-0.11.71
19-0.2-0.21.72
200-0.51.7
Sheet8
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,399
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Mar 4, 2020
Messages
51
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
12,399
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Mar 4, 2020
Messages
51
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,399
Office Version
  1. 365
Platform
  1. Windows
This one for positive values, same changes as before if you need negative as well.

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

Watch MrExcel Video

Forum statistics

Threads
1,126,932
Messages
5,621,676
Members
415,849
Latest member
PhoenixRising2015

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top