| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Consecutive Numbers
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Consecutive Numbers
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" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Consecutive Numbers
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" wrote: 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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Consecutive Numbers
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" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Consecutive Numbers
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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Consecutive Numbers
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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| consecutive numbers | Excel Worksheet Functions | |||
| how to add consecutive positive numbers | Excel Discussion (Misc queries) | |||
| generate consecutive numbers | New Users to Excel | |||
| consecutive numbers | Excel Worksheet Functions | |||
| Count Consecutive Numbers in a Row | Excel Worksheet Functions | |||
Hybrid Mode
