Count consecutive number with largest value and sum this

M

meohen1992

New Member
Joined
Jul 30, 2023
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hi everyone,
I am trying to find consecutive numbers with largest value.
I have used the formula then did not worked exactly like I want:
=MAX(FREQUENCY(IF(A2:A26>0,ROW(A2:A26)),IF(A2:A26<=0,ROW(A2:A26))))

This formula return longest sequence of numbers, not consecutive numbers with largest value...
The result I want is : 6 and 3839

Thanks,
Huy
Book1
ABCDEF
1ValueConsecutive positiveSum thisConsecutive with largest numberSum this
2623111,78563,839
3212Result Formula I want
4109
5291
6280
72,325
8-1,575
9632
101,287
11-1,175
12368
13-285
14-497
15-9
16429
17173
1890
1918
2057
2110
2263
23444
24410
2569
2622
Sheet1
Cell Formulas
RangeFormula
B2B2=MAX(FREQUENCY(IF(A2:A26>0,ROW(A2:A26)),IF(A2:A26<=0,ROW(A2:A26))))
C2C2=MAX((COUNTIF(OFFSET(A2:A26,ROW(A2:A26)-ROW(A2),0,B2),">=0")=B2)*SUBTOTAL(109,OFFSET(A2:A26,ROW(A2:A26)-ROW(A2),0,B2)))
E2E2=COUNT(A2:A7)
F2F2=SUM(A2:A7)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F2Cell Value>0textNO
F2Cell Value<0textNO
C2Cell Value>0textNO
C2Cell Value<0textNO
A2:A26Cell Value>0textNO
A2:A26Cell Value<0textNO
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Try this for the largest consecutive total:

Excel Formula: 
=MAX(SCAN(0,A2:A26,LAMBDA(_x,_y,IF(_y<0,0,SUM(_x+_y)))))

Try this for the number of consecutives:

Excel Formula: 
=LET(_a,SCAN(0,A2:A26,LAMBDA(_w,_x,IF(_x<0,0,SUM(_w+_x)))),_b,INDEX(_a,SEQUENCE(ROWS(_a),,ROWS(_a),-1)),SUM(SIGN(SCAN(0,_b,LAMBDA(_y,_z,IF(_z=MAX(_a),_z,_y*_z))))))

I feel like I'm definitely missing something that would make the number of consecutives simpler, but this is what I've got for now.
 
Upvote 0
Another option:

Book1
ABCDEF
1ValueConsecutive positiveSum thisConsecutive with largest numberSum this
2
3622.51111785.1863839.36
4211.5Result Formula I want
5109.3
6291.31
7280
82324.74
9-1575
10631.5
111287.06
12-1175
13368.26
14-285
15-496.69
16-9
17428.98
18173.09
1990.02
2018
2156.63
2210.39
2363.26
24444.14
25410.3
2668.68
2721.69
28
Sheet3
Cell Formulas
RangeFormula
B3B3=MAX(FREQUENCY(IF(A3:A27>0,ROW(A3:A27)),IF(A3:A27<=0,ROW(A3:A27))))
C3C3=MAX((COUNTIF(OFFSET(A3:A27,ROW(A3:A27)-ROW(A3),0,B3),">=0")=B3)*SUBTOTAL(109,OFFSET(A3:A27,ROW(A3:A27)-ROW(A3),0,B3)))
E3:F3E3=LET(a,IF((A3:A26>0)*(A2:A25<=0),ROW(A3:A26)),b,IF((A3:A27>0)*(A4:A28<=0),ROW(A4:A28)),c,COUNT(a),s,SEQUENCE(c),sa,SMALL(a,s),sb,SMALL(b,s),d,SUBTOTAL(9,OFFSET(A1,sa-1,0,sb-sa)),e,MAX(d),f,MATCH(e,d,0),CHOOSE({1,2},INDEX(sb,f)-INDEX(sa,f),e))
Dynamic array formulas.


I also think this can be improved. Note that I added an empty line before the data.
 
Upvote 0
Marginal improvement on number of consecutives:

Excel Formula: 
=LET(_a,SCAN(0,A2:A26,LAMBDA(_x,_y,IF(_y<0,0,SUM(_x+_y)))),INDEX(SCAN(0,A2:A26,LAMBDA(_x,_y,IF(_y<0,0,SUM(_x+1)))),XMATCH(MAX(_a),_a)))

Dynamic array formula for both, because it's not much longer:

Excel Formula: 
=LET(_a,SCAN(0,A2:A26,LAMBDA(_x,_y,IF(_y<0,0,SUM(_x+_y)))),HSTACK(INDEX(SCAN(0,A2:A26,LAMBDA(_x,_y,IF(_y<0,0,SUM(_x+1)))),XMATCH(MAX(_a),_a)),MAX(_a)))
 
Upvote 0
Eric W said:
Another option:

Book1
ABCDEF
1ValueConsecutive positiveSum thisConsecutive with largest numberSum this
2
3622.51111785.1863839.36
4211.5Result Formula I want
5109.3
6291.31
7280
82324.74
9-1575
10631.5
111287.06
12-1175
13368.26
14-285
15-496.69
16-9
17428.98
18173.09
1990.02
2018
2156.63
2210.39
2363.26
24444.14
25410.3
2668.68
2721.69
28
Sheet3
Cell Formulas
RangeFormula
B3B3=MAX(FREQUENCY(IF(A3:A27>0,ROW(A3:A27)),IF(A3:A27<=0,ROW(A3:A27))))
C3C3=MAX((COUNTIF(OFFSET(A3:A27,ROW(A3:A27)-ROW(A3),0,B3),">=0")=B3)*SUBTOTAL(109,OFFSET(A3:A27,ROW(A3:A27)-ROW(A3),0,B3)))
E3:F3E3=LET(a,IF((A3:A26>0)*(A2:A25<=0),ROW(A3:A26)),b,IF((A3:A27>0)*(A4:A28<=0),ROW(A4:A28)),c,COUNT(a),s,SEQUENCE(c),sa,SMALL(a,s),sb,SMALL(b,s),d,SUBTOTAL(9,OFFSET(A1,sa-1,0,sb-sa)),e,MAX(d),f,MATCH(e,d,0),CHOOSE({1,2},INDEX(sb,f)-INDEX(sa,f),e))
Dynamic array formulas.


I also think this can be improved. Note that I added an empty line before the data.
Click to expand...
Thank you, your formula is like a charm. I can filter with this.
However, I don't know how to remove or modify blank Row 2 in your file. When I edited in my file, it have not worked.
Is that blank row what makes your formula work?
 
Upvote 0
Anonymous1378 said:
Marginal improvement on number of consecutives:

Excel Formula: 
=LET(_a,SCAN(0,A2:A26,LAMBDA(_x,_y,IF(_y<0,0,SUM(_x+_y)))),INDEX(SCAN(0,A2:A26,LAMBDA(_x,_y,IF(_y<0,0,SUM(_x+1)))),XMATCH(MAX(_a),_a)))

Dynamic array formula for both, because it's not much longer:

Excel Formula: 
=LET(_a,SCAN(0,A2:A26,LAMBDA(_x,_y,IF(_y<0,0,SUM(_x+_y)))),HSTACK(INDEX(SCAN(0,A2:A26,LAMBDA(_x,_y,IF(_y<0,0,SUM(_x+1)))),XMATCH(MAX(_a),_a)),MAX(_a)))
Click to expand...
Thank you for your help,
Your solution is efficient and concise.
Could your formula add filter function as subtotal?
Thanks.
 
Upvote 0
meohen1992 said:
Thank you for your help,
Your solution is efficient and concise.
Could your formula add filter function as subtotal?
Thanks.
Click to expand...
I didn't even realize that was an intended feature of the formula. It gets a fair bit longer with the subtotal stuff thrown in:
Excel Formula: 
=LET(
_q,A3:A27,
_r,SUBTOTAL(109,OFFSET(_q,ROW(_q)-MIN(ROW(_q)),,1)),
_a,SCAN(0,_r,LAMBDA(_x,_y,IF(_y<0,0,SUM(_x+_y)))),
HSTACK(INDEX(SCAN(0,_r,LAMBDA(_x,_y,IF(_y<0,0,SUM(_x+1)))),XMATCH(MAX(_a),_a)),MAX(_a)))
 
Upvote 1
Solution
Anonymous1378 said:
I didn't even realize that was an intended feature of the formula. It gets a fair bit longer with the subtotal stuff thrown in:
Excel Formula: 
=LET(
_q,A3:A27,
_r,SUBTOTAL(109,OFFSET(_q,ROW(_q)-MIN(ROW(_q)),,1)),
_a,SCAN(0,_r,LAMBDA(_x,_y,IF(_y<0,0,SUM(_x+_y)))),
HSTACK(INDEX(SCAN(0,_r,LAMBDA(_x,_y,IF(_y<0,0,SUM(_x+1)))),XMATCH(MAX(_a),_a)),MAX(_a)))
Click to expand...
Thank you very much. It's working. :love:(y)
Is it possible to use this function to find the sequence of consecutive numbers with the smallest value?
As the above example is A12:A16 with the result is Count: 5 ; Total: -1597.46
 
Upvote 0
Another option
Fluff.xlsm
ABC
1ValueConsecutive positiveSum this
2622.5163839.36
3211.53-2790.69
4109.3
5291.31
6280
72324.74
8-1575
9631.5
101287.06
11-1175
12368.26
13-285
14-2496.69
15-9
16428.98
17173.09
1890.02
1918
2056.63
2110.39
2263.26
23444.14
24410.3
2568.68
2621.69
Master
Cell Formulas
RangeFormula
B2:C2B2=LET(d,A2:A26,r,ROWS(d),s,SEQUENCE(r),a,XMATCH(MAX(d),d),b,IFNA(XMATCH(TRUE,A2:INDEX(d,a)<=0,,-1),0),c,IFNA(XMATCH(TRUE,INDEX(d,a):A26<=0)+a-1,r+1),x,FILTER(d,(s>b)*(s<c)),HSTACK(ROWS(x),SUM(x)))
B3:C3B3=LET(d,A2:A26,r,ROWS(d),s,SEQUENCE(r),a,XMATCH(MIN(d),d),b,IFNA(XMATCH(TRUE,A2:INDEX(d,a)>=0,,-1),0),c,IFNA(XMATCH(TRUE,INDEX(d,a):A26>=0)+a-1,r+1),x,FILTER(d,(s>b)*(s<c)),HSTACK(ROWS(x),SUM(x)))
Dynamic array formulas.
 
Upvote 0
I modified Anonymous1378'S formula and got the solution I wanted to include both the largest and the smallest continuous numbers.
Sincere thanks to everyone who helped.
Regards,
Huy
TRADING MANAGEMENT - Tojei.xlsx
ABCDEFGHI
1ValueConsecutive positiveSum thisConsecutive with largest numberSum thisConsecutive with smallest numberSum this
2622.51111785.1863839.361-1575
3211.5
4109.3
5291.31
6280
72324.74
8-1575
9631.5
101287.06
11-1175
12368.26
13-285
14-496.69
15-9
16428.98
17173.09
1890.02
1918
2056.63
2110.39
2263.26
23444.14
24410.3
2568.68
2621.69
Sheet3
Cell Formulas
RangeFormula
B2B2=MAX(FREQUENCY(IF(A2:A26>0,ROW(A2:A26)),IF(A2:A26<=0,ROW(A2:A26))))
C2C2=MAX((COUNTIF(OFFSET(A2:A26,ROW(A2:A26)-ROW(A2),0,B2),">=0")=B2)*SUBTOTAL(109,OFFSET(A2:A26,ROW(A2:A26)-ROW(A2),0,B2)))
E2:F2E2=LET( _q,A2:A26, _r,SUBTOTAL(109,OFFSET(_q,ROW(_q)-MIN(ROW(_q)),,1)), _a,SCAN(0,_r,LAMBDA(_x,_y,IF(_y<0,0,SUM(_x+_y)))), HSTACK(INDEX(SCAN(0,_r,LAMBDA(_x,_y,IF(_y<0,0,SUM(_x+1)))),XMATCH(MAX(_a),_a)),MAX(_a)))
H2:I2H2=LET( _q,A2:A26, _r,SUBTOTAL(109,OFFSET(_q,ROW(_q)-MIN(ROW(_q)),,1)), _a,SCAN(0,_r,LAMBDA(_x,_y,IF(_y>0,0,SUM(_x+_y)))), HSTACK(INDEX(SCAN(0,_r,LAMBDA(_x,_y,IF(_y<0,0,SUM(_x+1)))),XMATCH(MAX(_a),_a)),MIN(_a)))
Dynamic array formulas.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Consecutive formula, not counting most recent sequence
Replies
6
Views
190
jchick90
J
A
  • Question
Formula to count consecutive numbers in each row of a data source
Replies
3
Views
353
DanteAmor
DanteAmor
M
  • Solved
Count a number just the first time it appears in each string.
Replies
8
Views
135
StephenCrump
S
A
  • Question
formula help please: to return MAX number of consecutive 1's in column when the table filtered
Replies
4
Views
565
DanteAmor
DanteAmor
R
  • Solved
Connect periods with spilled result, where the result is same as the calculated result in previous row
2
Replies
15
Views
180
roelandwatteeuw
R

Forum statistics

Threads
1,215,125
Messages
6,123,195
Members
449,090
Latest member
bes000

Share this page

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
Back
Top