CODEWITHSUNDEEP
excelif-statementexcel-formulafrequency
topstuff
topstuff

Reputation: 129

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

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?

Upvotes: 3

Views: 98

Answers (2)

rachel
rachel

Reputation: 1994

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)

enter image description here

Upvotes: 1

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27438

Try using the following formula:

enter image description here

• 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))

Upvotes: 4

Related Questions