 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / March 2008Consecutive 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 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) > Hi, again > [quoted text clipped - 7 lines] > > Tks Hi Ron There will be no blank cells, and the number are in continuous cells and in consecutive order Tks for the help > Can you provide a few more details? > Should blank cells be ignored? [quoted text clipped - 22 lines] > > > > Tks 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) > Hi Ron > [quoted text clipped - 30 lines] >> > >> > Tks 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 > Thanks for the additional info.... > [quoted text clipped - 67 lines] > >> > > >> > Tks > 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) > Tks Ron > [quoted text clipped - 80 lines] >> >> > >> >> > Tks Ron, doesn´t work in the set:1;3;5;7;9;11;13 there are no 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 [quoted text clipped - 100 lines] > >> >> > > >> >> > Tks 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) > Ron, doesn´t work > [quoted text clipped - 105 lines] >> >> >> > >> >> >> > Tks 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 > I see what you mean. > [quoted text clipped - 121 lines] > >> >> >> > > >> >> >> > Tks 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) > Ron > [quoted text clipped - 135 lines] >> >> >> >> > >> >> >> >> > Tks Ron, your right, had something wrong Tks very much for your precious help Rgds > There's something wrong with your formula.... > I copied the formula from my post and pasted it into my worksheet. [quoted text clipped - 149 lines] > >> >> >> >> > > >> >> >> >> > Tks 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) > Ron, your right, had something wrong > [quoted text clipped - 155 lines] >> >> >> >> >> > >> >> >> >> >> > Tks |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.