Count number of consecutive numbers

[Martin Coates]

Hi All
I'm trying to work out a formula to count the number of consecutive numbers in a row of numbers e.g if I have a series of numbers, say 1, 3, 4 ,5 12, 15, 16 I want to know how many numbers are consecutive, in this example 3,4 +5 =3 consecutive numbers and 15 + 16 = 2 consecutive numbers. I've tried Frequency and Countif but can't seem to get the right result.
Any thoughts anyone?
Martin

 

[Martin Coates]

I probably should have added that I'm only interested in the longest sequence of consecutive numbers, in the example this would be 3 (3, 4 +5) but there might be occasions where the sequence contains two runs of the same number.

 

[Peter_SSs]

Like this? It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}.

Excel Workbook
	A	B	C
1	1		3
2	3
3	4
4	5
5	12
6	15
7	16
8
Count Consecutive

 

[Martin Coates]

Hi Peter
Thank you for your reply but it hasn't quite worked. This may be because my data is stored horizontally and I've incorrectly adapted the formula.

Product Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Formula Result
XR1-12 5 8 9 10 38 53 3 =1+MAX(FREQUENCY(B2:F2-A2:E2=1,ROW(B2:F2)),IF(B2:F2-A2:E2<>1,ROW(B2:F2)))
XR1-18A 3 12 15 29 51 58 4 =1+MAX(FREQUENCY(B3:F3-A3:E3=1,ROW(B3:F3)),IF(B3:F3-A3:E3<>1,ROW(B3:F3)))
B400 CB 8 26 27 29 49 58 5 =1+MAX(FREQUENCY(B4:F4-A4:E4=1,ROW(B4:F4)),IF(B4:F4-A4:E4<>1,ROW(B4:F4)))
PRT4 29 33 43 45 49 56 6 =1+MAX(FREQUENCY(B5:F5-A5:E5=1,ROW(B5:F5)),IF(B5:F5-A5:E5<>1,ROW(B5:F5)))
KEY 2 10 19 28 29 31 7 =1+MAX(FREQUENCY(B6:F6-A6:E6=1,ROW(B6:F6)),IF(B6:F6-A6:E6<>1,ROW(B6:F6)))

Where have I gone wrong?
Cheers
Martin

 

[Peter_SSs]

Where have I gone wrong?

A few things ..
- You are including column A in the check but column A is not numerical,
- You didn't make the change from ROWS to COLUMNS in the formula, and
- You have lost the IF() function from the original

Try this

Excel Workbook
	A	B	C	D	E	F	G	H
1	Product	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Result
2	XR1-12	5	8	9	10	38	53	3
3	XR1-18A	3	12	15	29	51	58	1
4	B400 CB	8	26	27	29	49	58	2
5	PRT4	29	33	43	45	49	56	1
6	KEY	2	10	19	28	29	31	2
Count Consecutive (2)

 

[Martin Coates]

Peter, that's perfect, thank you. As you can probably tell I'm fairly new to excel, despite being 62 but they say you're never too old to learn something new.
Your help is much appreciated.
Cheers
Martin

 

[Peter_SSs]

Peter, that's perfect, thank you.

You are very welcome.

.. despite being 62 but they say you're never too old to learn something new.

You are right - I'm still learning about Excel too and I'm older than you.