excelif-statementexcel-formulafrequency

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?

Solution

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