VBA - Total Number Positive Or Negative, Exclude 0 or Include 0

ibmy

Board Regular
Joined
Mar 4, 2020
Messages
55
Office Version
  1. 2016
Platform
  1. Windows
Hi Excel Experts,

I been using the formula but for 90k-110k 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.

4 processor4.png

4 processor5.png


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 90k-120k weekday data:

dx 2.13.21.xlsx
DEFGHIJK
1Set 1 (+)Exclude 0Include 0Set 2 (-)Exclude 0Include 0
2
30-0.1
40-0.2
50.31-0.433
601
70.3-0.1
80.323-0.22
9-10
10-0.10
110-0.3
12-0.5-0.52
13-0.204
14-0.10.8
150.31-0.3
1601-0.52
17-0.70
180.4-0.3
190.8-0.5
201.2-0.3
211.74-0.54
22040
23-0.2-0.317
240.6110.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.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
It's not clear what you are doing. You don't say what cells those formulas are in, and the formulas refer to columns A,B,&C, but they aren't in your sheet capture shown. That said, you are using SUM to current row for column B and C ... wouldn't it be more efficient to have a couple of simple running total columns so that massive ranges are NOT referenced?
 

ibmy

Board Regular
Joined
Mar 4, 2020
Messages
55
Office Version
  1. 2016
Platform
  1. Windows
I put other data in Column A,B,C,G,H such time,sum,etc...so that vba provided here is not use that column.

The formula cell I currently use is, I put in another Sheet before I put the result in Column E,F,J,K in Sheet 1. Example, I open Sheet 2, to find Total Number Positive Exclude 0. Firstly, in Column A I put the data, Column B (=if(A3>0,1,""), then Column C (=IF(B3="","",IF(SUM($B$3:B3)=SUM($B$3:B4),SUM($B$3:B3)-SUM($C$2:C2),""))) ). The result I got in Column C in Sheet 2, i put in Sheet 1 Column E, Then continute opening new sheet for remaining =if(A3>=0,1,"") , =if(A3<0,1,""), =if(A3<=0,1,"").

p/s: Set 1(+) is Total Number Positive , Set 2(-) is Total Number Negative
 
Last edited:

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
You are being very vague and confusing ... you showed the formula as was used elsewhere, before you moved it to different columns, and then showed the layout after the move? That explains why your formulas don't match the sheet snip. But then you just repeat the formulas that showed before, and have not commented on my suggestion of using extra columns for running totals to prevent having to access massive ranges in formulas. I said that to reduce your calculation time ... which is what you requested in the first place.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,443
Messages
5,636,311
Members
416,912
Latest member
danluk12

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