Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count consecutive cells then start over
Hello, I would like to know how to create a simple function which will count
the cells that show up with the number 1 seven consecutive times and then start over. For instance, let's say the range is B3:B100, and the number 1 will occasionally show up 3x's or 5x's and then occasionally 7x's with blank cells and between each of the sets. I need it to display somewhere at the top of the spreadsheet when the number 1 has shown up seven consecutive times. Most importantly, I need it to start the count over again once it runs across a blank cell. Any help would greatly be appreciated! ~John~ |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count consecutive cells then start over
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count consecutive cells then start over
If a function is desired, use this formula in any cell desired to locate the
7th consecutive. =so("e") Put the below function into a REGULAR vba module Function so(mc) c = 0 For i = 2 To Cells(Rows.Count, mc).End(xlUp).Row If Cells(i + 1, mc) = 1 And Cells(i, mc) = 1 Then c = c + 1 Else c = 0 End If 'MsgBox c If c = 7 Then so = Cells(i, mc).Address 'Cells(1, mc) = Cells(i, mc).Address Exit For End If Next i End Function -- Don Guillett Microsoft MVP Excel SalesAid Software "Herbert Seidenberg" wrote in message ... Or without VBA and without counting 8x's 9x's ... http://www.freefilehosting.net/download/3aee3 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count consecutive cells then start over
Thanks Don, but unfortunately it didn't do anything. I do appreciate the
help all the same. "Don Guillett" wrote: If a function is desired, use this formula in any cell desired to locate the 7th consecutive. =so("e") Put the below function into a REGULAR vba module Function so(mc) c = 0 For i = 2 To Cells(Rows.Count, mc).End(xlUp).Row If Cells(i + 1, mc) = 1 And Cells(i, mc) = 1 Then c = c + 1 Else c = 0 End If 'MsgBox c If c = 7 Then so = Cells(i, mc).Address 'Cells(1, mc) = Cells(i, mc).Address Exit For End If Next i End Function -- Don Guillett Microsoft MVP Excel SalesAid Software "Herbert Seidenberg" wrote in message ... Or without VBA and without counting 8x's 9x's ... http://www.freefilehosting.net/download/3aee3 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count consecutive cells then start over
Try this array formula** :
=SUM(--(FREQUENCY(IF(List1=1,ROW(List1)),IF(List1<1,ROW( List1)))=Target)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Herbert Seidenberg" wrote in message ... Or without VBA and without counting 8x's 9x's ... http://www.freefilehosting.net/download/3aee3 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count consecutive cells then start over
It WAS tested.
I didn't but you may need this as the first line in the function. Application.Volatile Did you place in a regular module? Did you change "e" to your column that has the string of 1's? Is calculation set to automatic? Touch f9 key to calculate -- Don Guillett Microsoft MVP Excel SalesAid Software "John67" wrote in message ... Thanks Don, but unfortunately it didn't do anything. I do appreciate the help all the same. "Don Guillett" wrote: If a function is desired, use this formula in any cell desired to locate the 7th consecutive. =so("e") Put the below function into a REGULAR vba module Function so(mc) c = 0 For i = 2 To Cells(Rows.Count, mc).End(xlUp).Row If Cells(i + 1, mc) = 1 And Cells(i, mc) = 1 Then c = c + 1 Else c = 0 End If 'MsgBox c If c = 7 Then so = Cells(i, mc).Address 'Cells(1, mc) = Cells(i, mc).Address Exit For End If Next i End Function -- Don Guillett Microsoft MVP Excel SalesAid Software "Herbert Seidenberg" wrote in message ... Or without VBA and without counting 8x's 9x's ... http://www.freefilehosting.net/download/3aee3 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count consecutive cells then start over
Also, try this modification.
Function so(mc) Application.Volatile c = 1 For i = 2 To Cells(Rows.Count, mc).End(xlUp).Row If Cells(i + 1, mc) = 1 And Cells(i, mc) = 1 Then c = c + 1 Else c = 1 End If 'MsgBox c If c = 7 Then so = Cells(i + 1, mc).Address 'Cells(1, mc) = Cells(i, mc).Address Exit For End If Next i End Function -- Don Guillett Microsoft MVP Excel SalesAid Software "John67" wrote in message ... Thanks Don, but unfortunately it didn't do anything. I do appreciate the help all the same. "Don Guillett" wrote: If a function is desired, use this formula in any cell desired to locate the 7th consecutive. =so("e") Put the below function into a REGULAR vba module Function so(mc) c = 0 For i = 2 To Cells(Rows.Count, mc).End(xlUp).Row If Cells(i + 1, mc) = 1 And Cells(i, mc) = 1 Then c = c + 1 Else c = 0 End If 'MsgBox c If c = 7 Then so = Cells(i, mc).Address 'Cells(1, mc) = Cells(i, mc).Address Exit For End If Next i End Function -- Don Guillett Microsoft MVP Excel SalesAid Software "Herbert Seidenberg" wrote in message ... Or without VBA and without counting 8x's 9x's ... http://www.freefilehosting.net/download/3aee3 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count consecutive cells then start over
I withdraw my previous post.
Biff's approach is the way to go. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count consecutive cells then start over
flipping subjects a bit I am looking to count the number of "y" in a row but then start over at a blank cell. can the formula skip every other cell? any formulas available?
i.e. Attendance E2="Aug 21" G2="Aug 28" I2="Sept 4" E5="Class" F5="homework" G5="Class" H5="homework" I5="Class" J5="homework" C6="John" E6="y" E5="y" G6="y" H5="" I6="" J6="" the first formula should show that John has been to class 2xs in a row the second formula should show homework turned 1 out of 3 times. Is this possible? thanks again for any attempt. Daniel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count text in non consecutive cells in column | Excel Discussion (Misc queries) | |||
count text in non consecutive cells in column | Excel Discussion (Misc queries) | |||
count text in non consecutive cells in column | Excel Discussion (Misc queries) | |||
Count Consecutive Cells | Excel Discussion (Misc queries) | |||
How do I add consecutive numbers but start over at a break in the. | Excel Discussion (Misc queries) |