ibmy
Board Regular
 Joined
 Mar 4, 2020
 Messages
 55
 Office Version

 2016
 Platform

 Windows
Hi Excel Experts,
I been using the formula but for 90k110k row it took 9 minutes for 1 column. The time takes longer if I open many programs ( tabs Chrome, Spotify,etc..) My laptop's fan is spining fast and louder when apply the formula. For 20k row is ok , less than 10 seconds.
The formula I use currently is
1) =if(A3>0,1,"") , =if(A3>=0,1,"") , =if(A3<0,1,""), =if(A3<=0,1,"")
2) Then apply =IF(B3="","",IF(SUM($B$3:B3)=SUM($B$3:B4),SUM($B$3:B3)SUM($C$2:C2),""))
My sample of 90k120k weekday data:
Rule for Total Number of Negative ( Exclude 0 ) :
1. Start with Negative value.
2. Stop count if value are Positive or 0 ( >=0 )
3. Start new find for Negative value on next row.
Rule for Total Number of Negative ( Include 0 ) :
1. Start with Negative value.
2. Stop count if value is Positive ( >0 )
3. Start new find for Negative value on next row.
Rule for Sum Postive (Exclude 0,<=0 & Include 0,<0 ) same as above, just opposite of it.
Column A,B,C,G,H have other data.
Similiar to this thread is Sum of Negative or Positive, Exclude 0 or Include 0 , but it for sum and only 1 set data.
If there any formula that simplier or use less resources is also welcomed.
Thanks.
I been using the formula but for 90k110k row it took 9 minutes for 1 column. The time takes longer if I open many programs ( tabs Chrome, Spotify,etc..) My laptop's fan is spining fast and louder when apply the formula. For 20k row is ok , less than 10 seconds.
The formula I use currently is
1) =if(A3>0,1,"") , =if(A3>=0,1,"") , =if(A3<0,1,""), =if(A3<=0,1,"")
2) Then apply =IF(B3="","",IF(SUM($B$3:B3)=SUM($B$3:B4),SUM($B$3:B3)SUM($C$2:C2),""))
My sample of 90k120k weekday data:
dx 2.13.21.xlsx  

D  E  F  G  H  I  J  K  
1  Set 1 (+)  Exclude 0  Include 0  Set 2 ()  Exclude 0  Include 0  
2  
3  0  0.1  
4  0  0.2  
5  0.3  1  0.4  3  3  
6  0  1  
7  0.3  0.1  
8  0.3  2  3  0.2  2  
9  1  0  
10  0.1  0  
11  0  0.3  
12  0.5  0.5  2  
13  0.2  0  4  
14  0.1  0.8  
15  0.3  1  0.3  
16  0  1  0.5  2  
17  0.7  0  
18  0.4  0.3  
19  0.8  0.5  
20  1.2  0.3  
21  1.7  4  0.5  4  
22  0  4  0  
23  0.2  0.3  1  7  
24  0.6  1  1  0.1  
Sheet2 
Rule for Total Number of Negative ( Exclude 0 ) :
1. Start with Negative value.
2. Stop count if value are Positive or 0 ( >=0 )
3. Start new find for Negative value on next row.
Rule for Total Number of Negative ( Include 0 ) :
1. Start with Negative value.
2. Stop count if value is Positive ( >0 )
3. Start new find for Negative value on next row.
Rule for Sum Postive (Exclude 0,<=0 & Include 0,<0 ) same as above, just opposite of it.
Column A,B,C,G,H have other data.
Similiar to this thread is Sum of Negative or Positive, Exclude 0 or Include 0 , but it for sum and only 1 set data.
If there any formula that simplier or use less resources is also welcomed.
Thanks.