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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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

D
  • Solved
Counting Consecutive Blanks
Replies
9
Views
708
felixstraube
F
R
  • Solved
Count of consecutive months same data value appears
Replies
1
Views
227
rhonllanders
R
J
  • Question Question
Counting Number of Consecutive Negative/Positive Numbers in a Row
Replies
6
Views
492
Cubist
Cubist
N
  • Question Question
Summing of numbers with text.
Replies
4
Views
172
Fluff
Fluff
J
  • Solved
Consecutive formula, not counting most recent sequence
Replies
6
Views
471
jchick90
J

Forum statistics

Threads
1,224,820
Messages
6,181,160
Members
453,021
Latest member
Justyna P

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