Need formula or VBA count the delays

motilulla · Jan 26, 2019

Hello,

Note: Colours in column D are filled just to explain the example purpose 

In the column D I got some sequence of numbers and I got a formula, which counts the length of 0's in the G6:G19 now I need a formula, which can count delay of the 0's length in H6:H19

For example delay of the 0 with length 1, last 0 find in row 59 and the last data row is 65 so from the row "59 to 65" 0 is getting delay 6 

For example delay of the 0 with length 2, last 00 find in row 21&22 and the last data row is 65 so from the row "22 to 65" 00 is getting delay 43 and so on for all delays...

Here is an example...

Book1

A

B

C

D

E

F

G

H

1

2

3

4

5

Of 1's

Length Of 0

Count Of 0

Delay

6

0

1

8

6

7

1

2

43

8

2

3

2

1

9

3

4

0

10

4

5

0

11

0

6

1

32

12

1

7

0

13

2

8

0

14

0

9

0

15

0

10

1

10

16

0

11

0

17

1

12

0

18

0

13

0

19

0

14

0

20

1

21

0

22

0

23

1

24

0

25

1

26

0

27

1

28

0

29

0

30

0

31

0

32

0

33

0

34

1

35

0

36

1

37

0

38

1

39

2

40

3

41

0

42

1

43

2

44

3

45

4

46

0

47

0

48

0

49

0

50

0

51

0

52

0

53

0

54

0

55

0

56

1

57

2

58

3

59

0

60

1

61

2

62

0

63

0

64

0

65

1

Sheet4

Cell Formulas
Range	Formula
G6	{=SUM(0+(FREQUENCY(IF($D$6:$D$65=0,ROW($D$6:$D$65)),IF($D$6:$D$65<>0,ROW($D$6:$D$65)))=F6))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you all

Excel 2000
Regards,
Moti

motilulla · Jan 27, 2019

Akuini · Jan 27, 2019

Try this:

Code:

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1085206a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1085206-need-formula-vba-count-delays.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], j [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], n [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] va, vb, vc

n = Range([COLOR=brown]"D"[/COLOR] & Rows.count).[COLOR=Royalblue]End[/COLOR](xlUp).Row

va = Range([COLOR=brown]"D1:D"[/COLOR] & n + [COLOR=crimson]1[/COLOR])
[COLOR=Royalblue]ReDim[/COLOR] vb([COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]1[/COLOR])

[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]6[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR]) - [COLOR=crimson]1[/COLOR]

    [COLOR=Royalblue]If[/COLOR] va(i, [COLOR=crimson]1[/COLOR]) = [COLOR=crimson]0[/COLOR] [COLOR=Royalblue]Then[/COLOR]
    x = [COLOR=crimson]1[/COLOR]
        [COLOR=Royalblue]Do[/COLOR] [COLOR=Royalblue]While[/COLOR] va(i + [COLOR=crimson]1[/COLOR], [COLOR=crimson]1[/COLOR]) = [COLOR=crimson]0[/COLOR]
        x = x + [COLOR=crimson]1[/COLOR]: i = i + [COLOR=crimson]1[/COLOR]
        [COLOR=Royalblue]Loop[/COLOR]
    vb(i, [COLOR=crimson]1[/COLOR]) = x
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]

[COLOR=Royalblue]Next[/COLOR]

[COLOR=Royalblue]ReDim[/COLOR] vc([COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]14[/COLOR], [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]1[/COLOR])

[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]14[/COLOR]
    [COLOR=Royalblue]For[/COLOR] j = n [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]6[/COLOR] [COLOR=Royalblue]Step[/COLOR] -[COLOR=crimson]1[/COLOR]
        [COLOR=Royalblue]If[/COLOR] vb(j, [COLOR=crimson]1[/COLOR]) = i [COLOR=Royalblue]Then[/COLOR]
            vc(i, [COLOR=crimson]1[/COLOR]) = n - j: [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]For[/COLOR]
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
    [COLOR=Royalblue]Next[/COLOR]
[COLOR=Royalblue]Next[/COLOR]

Range([COLOR=brown]"H6"[/COLOR]).Resize(UBound(vc, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR]) = vc
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

Bo_Ry · Jan 27, 2019

Please try at H6 Press Ctrl+Shift+Enter and drag down

=IFERROR(COUNT($D$6:$D$99)+ROW($D$6)-SMALL(IF($D$6:$D$99<>0,ROW($D$6:$D$99)),MATCH(2,1/(FREQUENCY(IF($D$6:$D$99=0,ROW($D$6:$D$99)),IF($D$6:$D$99<>0,ROW($D$6:$D$99)))=F6))),"")

motilulla · Jan 27, 2019

Bo_Ry said:
Please try at H6 Press Ctrl+Shift+Enter and drag down

=IFERROR(COUNT($D$6:$D$99)+ROW($D$6)-SMALL(IF($D$6:$D$99<>0,ROW($D$6:$D$99)),MATCH(2,1/(FREQUENCY(IF($D$6:$D$99=0,ROW($D$6:$D$99)),IF($D$6:$D$99<>0,ROW($D$6:$D$99)))=F6))),"")

Hello Bo_Ry, I think function "=IFERROR" does not work due to I am using excel 2000

Thank you for your help

Kind Regards
Moti

Bo_Ry · Jan 27, 2019

You may ignore the error shown, press Ctrl+Shift+Enter

=COUNT($D$6:$D$99)+ROW($D$6)-SMALL(IF($D$6:$D$99<>0,ROW($D$6:$D$99)),MATCH(2,1/(FREQUENCY(IF($D$6:$D$99=0,ROW($D$6:$D$99)),IF($D$6:$D$99<>0,ROW($D$6:$D$99)))=F6)))

or
=IF(ISERROR(COUNT($D$6:$D$99)+ROW($D$6)-SMALL(IF($D$6:$D$99<>0,ROW($D$6:$D$99)),MATCH(2,1/(FREQUENCY(IF($D$6:$D$99=0,ROW($D$6:$D$99)),IF($D$6:$D$99<>0,ROW($D$6:$D$99)))=F6)))),"",COUNT($D$6:$D$99)+ROW($D$6)-SMALL(IF($D$6:$D$99<>0,ROW($D$6:$D$99)),MATCH(2,1/(FREQUENCY(IF($D$6:$D$99=0,ROW($D$6:$D$99)),IF($D$6:$D$99<>0,ROW($D$6:$D$99)))=F6))))

motilulla · Jan 27, 2019

Akuini said:

Try this:

Code:

[FONT=lucida console][COLOR=royalblue]Sub[/COLOR] a1085206a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1085206-need-formula-vba-count-delays.html[/COLOR][/I]
[COLOR=royalblue]Dim[/COLOR] i [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Long[/COLOR], j [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Long[/COLOR], n [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Long[/COLOR]
[COLOR=royalblue]Dim[/COLOR] va, vb, vc

n = Range([COLOR=brown]"D"[/COLOR] & Rows.count).[COLOR=royalblue]End[/COLOR](xlUp).Row

va = Range([COLOR=brown]"D1:D"[/COLOR] & n + [COLOR=crimson]1[/COLOR])
[COLOR=royalblue]ReDim[/COLOR] vb([COLOR=crimson]1[/COLOR] [COLOR=royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR] [COLOR=royalblue]To[/COLOR] [COLOR=crimson]1[/COLOR])

[COLOR=royalblue]For[/COLOR] i = [COLOR=crimson]6[/COLOR] [COLOR=royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR]) - [COLOR=crimson]1[/COLOR]

    [COLOR=royalblue]If[/COLOR] va(i, [COLOR=crimson]1[/COLOR]) = [COLOR=crimson]0[/COLOR] [COLOR=royalblue]Then[/COLOR]
    x = [COLOR=crimson]1[/COLOR]
        [COLOR=royalblue]Do[/COLOR] [COLOR=royalblue]While[/COLOR] va(i + [COLOR=crimson]1[/COLOR], [COLOR=crimson]1[/COLOR]) = [COLOR=crimson]0[/COLOR]
        x = x + [COLOR=crimson]1[/COLOR]: i = i + [COLOR=crimson]1[/COLOR]
        [COLOR=royalblue]Loop[/COLOR]
    vb(i, [COLOR=crimson]1[/COLOR]) = x
    [COLOR=royalblue]End[/COLOR] [COLOR=royalblue]If[/COLOR]

[COLOR=royalblue]Next[/COLOR]

[COLOR=royalblue]ReDim[/COLOR] vc([COLOR=crimson]1[/COLOR] [COLOR=royalblue]To[/COLOR] [COLOR=crimson]14[/COLOR], [COLOR=crimson]1[/COLOR] [COLOR=royalblue]To[/COLOR] [COLOR=crimson]1[/COLOR])

[COLOR=royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=royalblue]To[/COLOR] [COLOR=crimson]14[/COLOR]
    [COLOR=royalblue]For[/COLOR] j = n [COLOR=royalblue]To[/COLOR] [COLOR=crimson]6[/COLOR] [COLOR=royalblue]Step[/COLOR] -[COLOR=crimson]1[/COLOR]
        [COLOR=royalblue]If[/COLOR] vb(j, [COLOR=crimson]1[/COLOR]) = i [COLOR=royalblue]Then[/COLOR]
            vc(i, [COLOR=crimson]1[/COLOR]) = n - j: [COLOR=royalblue]Exit[/COLOR] [COLOR=royalblue]For[/COLOR]
        [COLOR=royalblue]End[/COLOR] [COLOR=royalblue]If[/COLOR]
    [COLOR=royalblue]Next[/COLOR]
[COLOR=royalblue]Next[/COLOR]

Range([COLOR=brown]"H6"[/COLOR]).Resize(UBound(vc, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR]) = vc
[COLOR=royalblue]End[/COLOR] [COLOR=royalblue]Sub[/COLOR][/FONT]

Excellant! Akuini, with your code I got the results perfect! 

Thank you for your help

Kind Regards
Moti

motilulla · Jan 27, 2019

Bo_Ry said:
You may ignore the error shown, press Ctrl+Shift+Enter

=COUNT($D$6:$D$99)+ROW($D$6)-SMALL(IF($D$6:$D$99<>0,ROW($D$6:$D$99)),MATCH(2,1/(FREQUENCY(IF($D$6:$D$99=0,ROW($D$6:$D$99)),IF($D$6:$D$99<>0,ROW($D$6:$D$99)))=F6)))

or
=IF(ISERROR(COUNT($D$6:$D$99)+ROW($D$6)-SMALL(IF($D$6:$D$99<>0,ROW($D$6:$D$99)),MATCH(2,1/(FREQUENCY(IF($D$6:$D$99=0,ROW($D$6:$D$99)),IF($D$6:$D$99<>0,ROW($D$6:$D$99)))=F6)))),"",COUNT($D$6:$D$99)+ROW($D$6)-SMALL(IF($D$6:$D$99<>0,ROW($D$6:$D$99)),MATCH(2,1/(FREQUENCY(IF($D$6:$D$99=0,ROW($D$6:$D$99)),IF($D$6:$D$99<>0,ROW($D$6:$D$99)))=F6))))

Thank you Bo_Ry, both the formula worked perfect!

Kind Regards
Moti

motilulla · Jan 27, 2019

motilulla said:
Excellant! Akuini, with your code I got the results perfect! 

Thank you for your help

Kind Regards
Moti

Akuini, find that if the data in column D finishes with 0 at the end instead 1 as shown in the post#1 VBA stop at the line below

Code:

 Do While va(i + 1, 1) = 0

Please can you check it?

Thank you

Kind Regards
Moti

Akuini · Jan 27, 2019

motilulla said:
Akuini, find that if the data in column D finishes with 0 at the end instead 1 as shown in the post#1 VBA stop at the line below

Rich (BB code):

Do While va(i + 1, 1) = 0

Please can you check it?

Thank you

Kind Regards
Moti

Ah, you're right, please add the blue line:

Code:

        Do While va(i + 1, 1) = 0
        x = x + 1: i = i + 1
        [COLOR=#0000ff]If i = n Then Exit Do[/COLOR]
        Loop

Need formula or VBA count the delays

motilulla

Well-known Member

Excel Facts

motilulla

Well-known Member

Akuini

Well-known Member

Bo_Ry

Board Regular

motilulla

Well-known Member

Bo_Ry

Board Regular

motilulla

Well-known Member

motilulla

Well-known Member

motilulla

Well-known Member

Akuini

Well-known Member

Similar threads

Forum statistics

Share this page

Need formula or VBA count the delays

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock