Identifying number of sequential numbers in a set of data in Excel

Question

I'm looking for a formula or set of formula to find how many numbers are found to be sequential.

See picture for example of what i'm looking for

Ive found the following formula that identifies what numbers are sequential. but it doesnt tell how many are in each range

=IF(A3=A2+1,C2,IF(A3=A4-1,MAX(C$1:C1)+1,""))

Answer 1

I use a helper column for this and the first row must be empty to get the correct result in the first data row.(reference to the previous row)

In the example data are in A2:A18, adjust the range for MATCH as required.

In B2: =IF(--OR(A2=(A3-1),A2=(A1+1))>0,MATCH(0,B3:B$18,0),0)

In C2: =IF(B2>0,MAX(C1,B2),0)

and copy down.

Answer 2

If you need to do this without Excel 365, you probably need Frequency. The idea is to get the length of each run using Frequency then index the resulting array by the number of runs that have occurred so far:

=INDEX(FREQUENCY(IF(A$3:A$19-A$2:A$18=1,ROW(A$2:A$18)),IF(A$3:A$19-A$2:A$18<>1,ROW(A$2:$A18)))+1,SUM(--(A$2:A2-A$1:A1<>1)))

This gives 1 for a 'run of length 1' (i.e. a single number which is not part of a run).

If you need to display a different value for a run of length 1, then without Let etc. you would need to repeat the Frequency part:

=INDEX((FREQUENCY(IF(A$3:A$19-A$2:A$18=1,ROW(A$2:A$18)),IF(A$3:A$19-A$2:A$18<>1,ROW(A$2:$A18)))+1)
*SIGN(FREQUENCY(IF(A$3:A$19-A$2:A$18=1,ROW(A$2:A$18)),IF(A$3:A$19-A$2:A$18<>1,ROW(A$2:$A18)))),
SUM(--(A$2:A2-A$1:A1<>1)))

Of course these would need to be array-entered.

---

EDIT

So the fix for both issues mentioned in the comment is just to use iferror (available in Excel 2007 and 2010). An error will always occur when the text in A1 is subtracted from the number in A2 so the iferror will force the index to start at 1.

Formula A:

=INDEX(FREQUENCY(IF(A$3:A$19-A$2:A$18=1,ROW(A$2:A$18)),IF(A$3:A$19-A$2:A$18<>1,ROW(A$2:$A18)))+1,
SUM(--IFERROR(A$2:A2-A$1:A1<>1,1)))

Formula B:

=INDEX(IF(FREQUENCY(IF(A$3:A$19-A$2:A$18=1,ROW(A$2:A$18)),IF(A$3:A$19-A$2:A$18<>1,ROW(A$2:$A18)))>0,
FREQUENCY(IF(A$3:A$19-A$2:A$18=1,ROW(A$2:A$18)),IF(A$3:A$19-A$2:A$18<>1,ROW(A$2:$A18)))+1,""),
SUM(--IFERROR(A$2:A2-A$1:A1<>1,1)))