Sum Of Positive and Negative Sequence

[ibmy]

Hi,

14.7.xlsb
	A	B	C
1	data		expected result
2
3	-0.2
4	0.1	1
5	0.1	2	0.2
6	0
7	0
8	-0.1
9	0.1	1	0.1
10	-0.1
11	-0.1
12	0.1	1
13	0.2	2
14	0.1	3	0.4
15	-0.1
16	0.1	1	0.1
Sheet5

Need 2 formula which use Column A and Column B as reference.
To find out which formula run faster in my low spec laptop ?
Data have about 60-90k row daily.

Column A as reference:
Rule : Reset postive sequence sum when Column A value <= 0 & Reset negative sequence sum when Column A value >= 0

Column B as reference:
Check Column B value for 1,2,3,4,etc..., then take value from Column A to count. No count on blank cell.

p/s: I guess for negative sequence sum just changing the symbol (> / < ) in formula, right?

14.7.xlsb
	A	B	C
1	data		expected result
2
3	-0.1	1	-0.1
4	0.1
5	-0.1	1
6	-0.1	2	-0.2
7	0
8	-0.1	1
9	-0.1	2
10	-0.1	3	-0.3
11	0.1
Sheet6

Thank you.

 

[QuestionBaker]

Can you try this formula and let me know if works or not?

=IF(AND(ISBLANK(B3),ISNUMBER(B2)),SUM(OFFSET(A2,0,0,-B2)), "")

paste it in C2 and drag it down from there

PS. I am not sure how to make it light weight

here is a screencap of what I did

 

[ibmy]

@QuestionBaker Do not work.

The formula you made is using Column B as reference,right?

 

[QuestionBaker]

Yes, I assumed that column B already exists.

 

[QuestionBaker]

If you want the column B to be autrogenerate
Assuming is arranged in the way you showed it in first image,
B2 as =IF(AND(A2>0,ISNUMBER(B1)),B1+1,IF(AND(A2>0,NOT(ISBLANK(B1))),1, ""))
C3 as =IF(AND(NOT(ISNUMBER(B4)),ISNUMBER(B3)),SUM(OFFSET(A3,0,0,-B3)), "")
drag the formula as long as required.
Please keep in mind that formula in column B should start from B2
If it works, I would ask you to go through the results, and see if there aren't any mistakes, and post them if you find them
(I am also learning)

 

[Fluff]

How about

+Fluff New.xlsm
	A	B
1	data	expected result
2
3	-0.2
4	0.1
5	0.1	0.2
6	0
7	0
8	-0.1
9	0.1	0.1
10	-0.1
11	-0.1
12	0.1
13	0.2
14	0.1	0.4
15	-0.1
16	0.1	0.1
Data

Cell Formulas
Range	Formula
B3:B16	B3	=IF(AND(A3>0,A4<=0),SUMIF(A$3:A3,">0")-SUM(B$2:B2),"")

 

[ibmy]

@QuestionBaker & @Fluff Thanks for the formula. Both are working ?

I have 2 new request:

(1)

eu 7.9.xlsb
	A	B	C
1	data	formula 1	formula 2
2
3	-0.1
4	-0.2
5	0.2	0.2
6	0.1	0.3
7	0.1	0.4	0.4
8	-0.1
9	-0.1
10	0.1	0.1
11	0.1	0.2
12	0.1	0.3
13	0.2	0.5	0.5
14	0
15	0.1	0.1	0.1
16	-0.1
17	0.1	0.1
18	0.1	0.2	0.2
19	-0.1
Sheet3

Instead of display total sum of positive/negative value. It now display sum for each row as long as it not break the rule.

For postive value rule:
Reset sum when next row is <=0

Then, apply formula in Column C to show only max value for each sequence in Column B.

(2)

eu 7.9.xlsb
	A	B	C	D
1	data A	data B	formula 1	formula 2
2
3	0.5	0.1	0.5	0.5
4	0.3	-0.5
5	0.1	0.1	0.1
6	0.1	0.1	0.2
7	0.1	0.3	0.3	0.3
8	0	0
9	0.7	0
10	-0.9	0.5
11	-0.9	0
12	0.1	1	0.1
13	0.7	1.1	0.8	0.8
14	0	2
Sheet6

This data a bit advance since it need to consider other column as well.

For postive value rule:
Current Column A & B must positive value >0
Reset when next row or next column <=0

Then, apply formula in Column D to show only max value for each sequence from Column C.

 

[Fluff]

As this is a totally different question, please start a new thread.
Thanks

 

[QuestionBaker]

@Fluff I am blown away by how easy and concise you were able to keep the formula.