Consecutive Numbers

[Antonio]

Hi, again

data displayed as follows
Column A Column B Column D
1012 1013 1016

Is there a way to count how many consecutive numbers there are, so that when
the numbers change the consecutive count changes also??

Tks

 

[Ron Coderre]

Can you provide a few more details?
Should blank cells be ignored?
Could the consecutive values be: 1012, 1013, blank, 1014?
or must they be in contiguous cells?
Will the values be in consecutive order?
Or is this considered a sequence: 1013, 1015, 1014?

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Antonio]

Hi Ron

There will be no blank cells, and the number are in continuous cells and in
consecutive order

Tks for the help

 

[Ron Coderre]

Thanks for the additional info....

Try something like this:

With
A1:G1 contains numbers in ascending order that
may, or may not, be consecutive.

Example:
A1: 4
B1: 6
C1: 7
D1: 8
E1: 10
F1: 15
G1: 16

This ARRAY FORMULA (committed with CTRL+SHIFT+ENTER, instead of just ENTER)
returns the maximum count of consecutive numbers:

In sections for readability..
=MAX(FREQUENCY(IF(A1:F1=(B1:G1-1),COLUMN(A1:F1)),
IF(A1:F1<>(B1:G1-1),COLUMN(A1:F1))))+1

In the above example, the formula returns: 3
(6,7, and 8 are the longest run of consecutive numbers)

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Antonio]

Tks Ron

When I change the values for:
1;2;6;7;10;11;14 returns 2 as a result, and if no consecutive numbers
1;3;5;7;9;11;13 returns result 1

Copied and pasted the formula and same was entered as an array

Rgds

 

[Ron Coderre]

When I change the values for:

1;2;6;7;10;11;14 returns 2 as a result, and if no consecutive numbers
1;3;5;7;9;11;13 returns result 1

I'm not sure if you're telling me the formula works....or doesn't work.

With those original values...what were you expecting to see?
Do you want to know how many consecutive values
there are anywhere in the data?
(6 for the first example: 1;2_6;7_10;11)
(1..or 0...for the second example?)

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Antonio]

Ron, doesn´t work

in the set:1;3;5;7;9;11;13 there are no consecutive numbers

 

[Ron Coderre]

I see what you mean.

So, maybe this ARRAY FORMULA:
=MAX(FREQUENCY(IF(A1:F1=(B1:G1-1),COLUMN(A1:F1)),
IF(A1:F1<>(B1:G1-1),COLUMN(A1:F1))))+
(COUNT(1/(A1:F1=(B1:G1-1)))>0)

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Antonio]

Ron

If I change for the following nrs:
1,2,3,4,5,6,7... the result is 5
so, once again it doesn't work

Tks

 

[Ron Coderre]

There's something wrong with your formula....
I copied the formula from my post and pasted it into my worksheet.

Using 1;2;3;4;5;6;7 in cells A1:G1,
the formula returns: 7

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Antonio]

Ron, your right, had something wrong

Tks very much for your precious help

Rgds

 

[Ron Coderre]

Thanks for the update! I'm glad you got it working.
I didn't know WHAT else to recommend.

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)