Largest sum of consecutive values in row, but is it still consecutive in the cell of the last column?

Question

I have a row of non unique values in Excel 365, some cells are blank. I would like to scan the row and count consecutive cells where values exist.

W1	W2	W3	W4	W5	W6	W7	W8
1	2		1		1	2	3

So I've used the below array formula code:

{=MAX(FREQUENCY(IF(H4:P4>0,COLUMN(H4:P4)),IF(H4:P4=0,COLUMN(H4:P4))))}

Providing the answer of 3: (W6, W7 and W8).

I would now like to use a new cell to display whether this consecutive run has ended in the last column; W8.

How would I write this?

Could try: =LET(a,SCAN(,SIGN(H5:O5),LAMBDA(x,y,IF(y,x+1,0))),INDEX(H4:O4,MATCH(MAX(a),a,))) — Mayukh Bhattacharya
– Mayukh Bhattacharya, Commented Oct 18, 2024 at 15:22
@MayukhBhattacharya That's very useful, I've just tried it now. It displays the column name where the large consecutive row ends, thanks! — topstuff
– topstuff, Commented Oct 18, 2024 at 15:30

Mayukh Bhattacharya · Accepted Answer · 2024-10-18 16:40:42Z

4

Try using the following formula:

• Formula used in cell H7

=LET(a,SCAN(,SIGN(H5:O5),LAMBDA(x,y,IF(y,x+1,0))),XLOOKUP(MAX(a),a,H4:O4,""))

Or, as commented above:

=LET(a,SCAN(,SIGN(H5:O5),LAMBDA(x,y,IF(y,x+1,0))),INDEX(H4:O4,MATCH(MAX(a),a,)))

As suggested by Tom Sir, if there are two runs of three and want to see if one of them finishes in the last place, then the following formula should be applied.

=LET(a,SCAN(,SIGN(H5:O5),LAMBDA(x,y,IF(y,x+1,0))),XLOOKUP(MAX(a),a,H4:O4,"",,-1))

edited Oct 18, 2024 at 16:40

answered Oct 18, 2024 at 15:33

Mayukh Bhattacharya

29.1k9 gold badges31 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tom Sharpe Over a year ago

Nice. I guess you might consider this for the case where there are two runs of three and you want to see if one of them finishes in the last place =LET(a,SCAN(,SIGN(H5:O5),LAMBDA(x,y,IF(y,x+1,0))),XLOOKUP(MAX(a),a,H4:O4,"",,-1))

Mayukh Bhattacharya Over a year ago

@TomSharpe Sir, you are right, I will update. Thanks for the heads up

VBasic2008 Over a year ago

Great. OP said "... some cells are blank...". Wouldn't ISBLANK(H5:O5) with y,0,x+1 be more accurate? Also, maybe consider the case when the max repeats itself and the last max includes the last cell but the lookup, of course, chooses the first one i.e. getting closer to OP's requirement "... whether or not this consecutive run has ended in the last column" something like MAX(a)=TAKE(a,,-1).

Mayukh Bhattacharya Over a year ago

Wonderful, didnt thought of that way.

P.b Over a year ago

SIGN could be replaced with LEN to make it compatible with text also (not the question though)

rachel · Accepted Answer · 2024-10-20 04:21:00Z

1

You can also write SCAN() like below to count consecutive non-blank cells:

=SCAN(0,H4:O4,LAMBDA(aggregated,current, (aggregated+1)*(current<>"")))

To find the largest consecutive count, you can also use SORT() and TAKE():

=TAKE(SORT(VSTACK(H3:O3, SCAN(0, H4:O4, LAMBDA(aggregated, current, (aggregated + 1) * (current <> "")))), 2, -1, TRUE), 1, 1)

answered Oct 20, 2024 at 4:21

rachel

1,9941 gold badge12 silver badges10 bronze badges

Collectives™ on Stack Overflow

Largest sum of consecutive values in row, but is it still consecutive in the cell of the last column?

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related