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

[Clayton18]

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

Ive found the following formula that identifies what numbers are sequential. but it doesn't tell how many are in each range =IF(A3=A2+1,C2,IF(A3=A4-1,MAX(C$1:C1)+1,""))

 

[Peter_SSs]

Welcome to the MrExcel board!

You have shown us a "potential helper column (not row)", but what are your desired actual results for that sample data?

 

[Clayton18]

Welcome to the MrExcel board!

You have shown us a "potential helper column (not row)", but what are your desired actual results for that sample data?

Sorry, the "potential helper row" is the desired actual results. Im hoping for the second row to count the sequential numbers

 

[Peter_SSs]

second row

As I mentioned above, that is a column not a row (don't want to confuse your potential helpers with incorrect terminology )

I have also assumed that the very first value under the heading in the second column should be 4?
If so try the formula below, adjusting the ranges if required.

BTW, for the future , I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

23 12 12.xlsm
	A	B
1	Number	Result
2	2	4
3	3	4
4	4	4
5	5	4
6	56	2
7	57	2
8	4
9	52
10	1	3
11	2	3
12	3	3
13	5
14	20	5
15	21	5
16	22	5
17	23	5
18	24	5
19
20
21
Sequential

Cell Formulas
Range	Formula
B2:B18	B2	=IF(A2<>IFERROR(A1+1,"."),IF(A3<>A2+1,"",MATCH(TRUE,A3:A$21<>A2:A$20+1,0)),B1)

 

[Clayton18]

As I mentioned above, that is a column not a row (don't want to confuse your potential helpers with incorrect terminology )

I have also assumed that the very first value under the heading in the second column should be 4?
If so try the formula below, adjusting the ranges if required.

BTW, for the future , I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

23 12 12.xlsm
	A	B
1	Number	Result
2	2	4
3	3	4
4	4	4
5	5	4
6	56	2
7	57	2
8	4
9	52
10	1	3
11	2	3
12	3	3
13	5
14	20	5
15	21	5
16	22	5
17	23	5
18	24	5
19
20
21
Sequential

Cell Formulas
Range	Formula
B2:B18	B2	=IF(A2<>IFERROR(A1+1,"."),IF(A3<>A2+1,"",MATCH(TRUE,A3:A$21<>A2:A$20+1,0)),B1)

Sorry, I obviously had a brain fart there!
Your formula has absolutely saved my life. Thank you very much!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.