count # of cells that are zero if more than 10 consecutive cell have zeros in them (XP, Excel 2010)

[nearbeer]

All,

Thank you for your time for looking at my question.

I would like to count the # of consecutive cells in a column that have a zero in them if there are more than 10 consecutive cells with a value of zero. For example, I have a spreadsheet that records production data from a machine. Each row represents one minute of the day (17:30, 17:31, 17:32, etc). If there are 10 or more consecutive rows that have zero (0) production recorded, I want to tally up those minutes. For one of my days of production, I have about 44 rows that had production one minute, zero the next, production for the next 12 mins, and then I went 19 mins without production due to a malfunction, and production picked back up after the 33rd minute; there were 3 minutes (non-consecutive) after that in which there was zero production. In this case, there would be 19 mins of downtime.

Let's say I have a range of minutes in Col. A (A1:A30) and in col. B I have a record of production units (1 or 2 units; never more than 2). From A9:A22 there was zero production; I want to have a value of 1 in G9 to indicate at that particular minute, the machine was down because the 10 min trigger had been reached if one were to look at the next 10 minutes. Since there are more than 10 cells with values of zero in A9:A22, I want G9:G22 to sum up to 14 minutes.

I tried nesting several if statements together but I couldn't get them to work.

I apologize for not posting an example spreadsheet; I am on my home computer while my spreadsheets are on my work computer.

I truly appreciate your time in helping me out.

Thank you,

Adam

 

[shawnhet]

I believe that if you paste this in row 10 and fill down for your whole range, it should get you pretty close to what you are looking for.

=IF(COUNTIF(A1:A10,0)=10,B9+1,0)

Let me know if it works or needs some tweaking.

Cheers,

 

[cpg84]

So the cells in column G will each have this same formula in it, or is the formula in just one cell?

 

[Robert Mika]

All,

Thank you for your time for looking at my question.

I would like to count the # of consecutive cells in a column that have a zero in them if there are more than 10 consecutive cells with a value of zero. For example, I have a spreadsheet that records production data from a machine. Each row represents one minute of the day (17:30, 17:31, 17:32, etc). If there are 10 or more consecutive rows that have zero (0) production recorded, I want to tally up those minutes. For one of my days of production, I have about 44 rows that had production one minute, zero the next, production for the next 12 mins, and then I went 19 mins without production due to a malfunction, and production picked back up after the 33rd minute; there were 3 minutes (non-consecutive) after that in which there was zero production. In this case, there would be 19 mins of downtime.

Let's say I have a range of minutes in Col. A (A1:A30) and in col. B I have a record of production units (1 or 2 units; never more than 2). From A9:A22 there was zero production; I want to have a value of 1 in G9 to indicate at that particular minute, the machine was down because the 10 min trigger had been reached if one were to look at the next 10 minutes. Since there are more than 10 cells with values of zero in A9:A22, I want G9:G22 to sum up to 14 minutes.

I tried nesting several if statements together but I couldn't get them to work.

I apologize for not posting an example spreadsheet; I am on my home computer while my spreadsheets are on my work computer.

I truly appreciate your time in helping me out.

Thank you,

Adam

Excel 2010
	A	B	C	D
1	17:30			1
2	17:31			10
3	17:32
4	17:33
5	17:34
6	17:35
7	17:36
8	17:37
9	17:38
10	17:39
11	17:40
12	17:41
13	0:00
14	0:00
15	0:00
16	0:00
17	0:00
18	0:00
19	0:00
20	0:00
21	0:00
22	0:00
23	0:00
24	17:53
25	17:54
26	17:55
27	17:56
28	17:57
29	17:58
30	17:59
Sheet1

Cell Formulas
Range	Formula
D1	{=SUM(--(FREQUENCY(IF(A1:A30>=0,ROW(A1:A30)),IF(A1:A30<>0,ROW(A1:A30)))>=D2))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[shawnhet]

Cammy, if that's addressed to me, I was setting it up so as to copy it (or fill it) down for the whole range.

Cheers,

 

[dermie_72]

Try this in VB

Sub test()
Dim timing As Range
Dim zeroprod As Integer
Dim downtime As Integer
Rownum = ActiveSheet.UsedRange.Rows.Count
Set timing = Range("B1:B" & Rownum)
zeroprod = 0
downtime = 0
Range("B1").Select
For i = 1 To timing.Cells.Count
If ActiveCell = 0 Then
zeroprod = zeroprod + 1
If zeroprod = 10 Then
downtime = downtime + 1
zeroprod = 0
Else
End If
Else: zeroprod = 0
End If
ActiveCell.Offset(1, 0).Select
Next i
If downtime >= 1 Then
Range("G2").Select
ActiveCell = downtime
Else
End If
End Sub


There may be a nicer way to write this, but this should work.

Assuming:

Times are in Colum A
In column B is looks for 10 0's in a row.
if there are 10's in a row (or More) it will return the number of times 0 is repeat 10 times into G2.

Hope that helps.

 

[nearbeer]

All,

Many thanks for the responses so far; I will test them today and let you know the results.

Cammy84...my goal was to have the formula in each cell.

Again, thanks for everyone's help!!!

 

[shawnhet]

Looking over my expression above, I believe that I have missed the point of the OP. Please alter my expression to the following (placed in row 10 and copied down)

=IF(COUNTIF(A1:A10,0)=10,if(b9=0,10,B9+1),0)

Cheers,

 

[nearbeer]

All apologies for the delay in responding to everyone's help. Here is what a coworker came up with and it has worked for my needs:

I set my criteria for downtime to be any block of time that had zero production greater than 6 minutes.

To begin with, in column "L" I put the following:

=IF(AND([@ProductionCount]=0, C2=C3),1,"")

This told me whether or not in column "C" there was zero production for that minute ("C2") and the next minute ("C3"); returned a value of 1 if zero production.

Secondly, in column "M":

=IF(OR(L2="",L2<>L1),0,L2+M1).

This started counting the # of consecutive cells with zeros.

Finally, in column "N":

=IF(M2=6,7,IF(M2>=6,1,""))

This returned a value of 7 if a cell in col. "M" was 6 or a value of 1 if a cell in col. "M" was greater than 6. I had the cell return 7 because I wanted downtimes that were greater than 6 mins.

Many thanks for all that helped; it started me in the right direction.

/R,

Adam