Total number of occurrence of value "0" in repetitive

[ibmy]

Hi,

My interest value is "0" (zero) in repetitive and I would like to know total number of its occurrence.

I need 3 formula. Formula 2 is basically from output of Formula 1 and Formula 3 is s single formula that can straight count.

Book1
	A	B	C	D	E	F	G	H
1		data		Formula 1	Formula 2		Formula 3
2		0		1
3		0.2			1		1
4		1
5		0		1
6		0		1
7		0		1
8		-0.9			3		3
9		1.2
10		-3.2
11		0		1
12		0.1			1		1
13		0		1
14		0		1
15		-0.1			2		2
16		0		1
17					1		1
18
Sheet3

Thanks.

 

[Peter_SSs]

I wasn't clear whether you really want all 3 formulas, or only if it is not possible with a single formula. See if this helps.

21 01 02.xlsm
	A	B	C			F	G
1		data					Formula 3
2		0
3		0.2					1
4		1
5		0
6		0
7		0
8		-0.9					3
9		1.2
10		-3.2
11		0
12		0.1					1
13		0
14		0
15		-0.1					2
16		0
17							1
Count 0

Cell Formulas
Range	Formula
G2:G17	G2	=IF(AND(B1=0,OR(B2<>0,B2="")),COUNTIF(B$1:B1,0)-SUM(G$1:G1),"")

 

[ibmy]

Thanks @Peter_SSs . Formula 3 works fine but my old laptop took 2 minutes to complete the result of 90 000 row data.

From post #1, actually what I want is all 3 formula because I want to find out, is single formula work fast in this case of problem. From past years, formula I tested, my old laptop perform better if the formula is simple ( example are like Formula 1 and Formula 2).

Can I request a new formula below, I need both Formula 2 and Formula 3 using ouput from Column D result ( I use formula " =if(b2=0,1,0) ". The difference between Formula 2 and Formula 3 are , result formula 2 is next to end of "0" repetitive while Formula 3 is below it and both are using Column D result.

Book1
	A	B	C	D	E	F	G
1		data			Formula 1	Formula 2
2		0		1	1
3		0.2		0		1
4		1		0
5		0		1
6		0		1
7		0		1	3
8		-0.9		0		3
9		1.2		0
10		-3.2		0
11		0		1	1
12		0.1		0		1
13		0		1
14		0		1	2
15		-0.1		0		2
16		0		1	1
17						1
Sheet1

Cell Formulas
Range	Formula
D2:D16	D2	=IF(B2=0,1,0)

 

[Peter_SSs]

You could test these for a 2-step approach but the second formula has a fairly similar structure to my earlier one so it may not be any/much faster.

21 01 02.xlsm
	B	C	D	E
1	data		Formula 1	Formula 2
2	0		1
3	0.2		0	1
4	1		0
5	0		1
6	0		1
7	0		1
8	-0.9		0	3
9	1.2		0
10	-3.2		0
11	0		1
12	0.1		0	1
13	0		1
14	0		1
15	-0.1		0	2
16	0		1
17				1
Count 0

Cell Formulas
Range	Formula
D2:D16	D2	=--(B2=0)
E2:E17	E2	=IF(D1&-D2="10",SUM(D$2:D2)-SUM(E$1:E1),"")

But I do have some questions that might offer another approach:

1. At the moment each final result is on the row immediately after the sequence of "1"s. Would it matter if instead the final result was on the last row of the sequence like in column D below or on the first row of the sequence like in column E below?
   
   

   21 01 02.xlsm
   	B	C	D
   1	data	Last row	First row
   2	0	1	1
   3	0.2
   4	1
   5	0		3
   6	0
   7	0	3
   8	-0.9
   9	1.2
   10	-3.2
   11	0	1	1
   12	0.1
   13	0		2
   14	0	2
   15	-0.1
   16	0	1	1
   Sheet1

   
   
   
2. Are you able to put any limit on the possible length of the sequence of "1"s? That is, is it possible the there could a sequence of 90,000 or could you say for example there would never be more than 100 "1"s in a row?
   
   
3. If a macro could do the job faster would that be acceptable?

 

[ibmy]

Thanks for the formula.

"...so it may not be any/much faster."

Yeah you right , 2-step approach not any/much faster.

Thanks for suggest for another approach.
1. I would prefer "on the last row of the sequence"
2. As for now the data I had, the limit is no more than 50 but in the future I do not know yet.
3. Yes, a macro is acceptable.

 

[Peter_SSs]

Thanks for suggest for another approach.
1. I would prefer "on the last row of the sequence"

3. Yes, a macro is acceptable.

Give this a try. Results are put into column D.
I have put in a Message Box pop-up to let you know when it is finished and how long it took. It will depend on your machine but for me with 90,000 the result was less than 0.1 seconds.
If you want to persist with this code then you can remove the asterisked lines if you don't want the message box & time.

Sub Count_0_sequences()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
  Dim t As Single '********************
  
  t = Timer       '********************
  a = Range("B1", Range("B" & Rows.Count).End(xlUp).Offset(1)).Value
  a(UBound(a), 1) = 1
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 2 To UBound(a)
    If a(i, 1) = 0 Then
      k = k + 1
    ElseIf a(i - 1, 1) = 0 Then
      b(i - 1, 1) = k
      k = 0
    End If
  Next i
  Range("D1").Resize(UBound(b)).Value = b
  MsgBox "Done in " & Format(Timer - t, "0.000 secs") '********************
End Sub

My (small) sample data and results:

ibmy.xlsm
	B	C	D
1	data
2	0		1
3	0.2
4	1
5	0
6	0
7	0		3
8	-0.9
9	1.2
10	-3.2
11	0		1
12	0.1
13	0
14	0		2
15	-0.1
16	0		1
17
Count 0

 

[Peter_SSs]

Hi, I see that the macro approach must have been okay for you as you have marked that as the accepted solution. That's good news but I was also wondering about the macro speed on your "old laptop" compared to the 2 minute formula approach?

 

[ibmy]

The macro is perfectly working and works efficiently ? ?, it save a lot of time. It save my everyday life 2 minutes for every data I have to deal.
The macro takes between 0.165-0.175 second on my old laptop. The time is varies between the data because some of data have 85k, 83k,89k and the max is 90k rows.

Thanks @Peter_SSs

 

[Peter_SSs]

You're welcome.

The macro takes between 0.165-0.175

Cheers. Less than 0.2 seconds is a good improvement.