Need formula or VBA count the delays

M

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,422
Office Version
  1. 2010
Hello,</SPAN></SPAN>

Note: Colours in column D are filled just to explain the example purpose </SPAN></SPAN>

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</SPAN></SPAN>

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 </SPAN></SPAN>

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...</SPAN></SPAN>

Here is an example... </SPAN></SPAN>


Book1
ABCDEFGH
1
2
3
4
5Of 1'sLength Of 0Count Of 0Delay
60186
712243
82321
9340
10450
1106132
12170
13280
14090
15010110
160110
171120
180130
190140
201
210
220
231
240
251
260
271
280
290
300
310
320
330
341
350
361
370
381
392
403
410
421
432
443
454
460
470
480
490
500
510
520
530
540
550
561
572
583
590
601
612
620
630
640
651
Sheet4
Cell Formulas
RangeFormula
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</SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>
 
Last edited:

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
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]
 
Upvote 0
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))),"")
 
Upvote 0
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))),"")
Click to expand...
Hello Bo_Ry, I think function "=IFERROR" does not work due to I am using excel 2000</SPAN></SPAN>

Thank you for your help</SPAN></SPAN>

Kind Regards</SPAN></SPAN>
Moti</SPAN></SPAN>
 
Upvote 0
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))))
 
Upvote 0
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]
Click to expand...
Excellant! Akuini, with your code I got the results perfect! </SPAN></SPAN>

Thank you for your help</SPAN></SPAN>

Kind Regards</SPAN></SPAN>
Moti :)</SPAN></SPAN>
 
Upvote 0
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))))
Click to expand...
Thank you Bo_Ry, both the formula worked perfect!</SPAN></SPAN>

Kind Regards</SPAN></SPAN>
Moti :)</SPAN></SPAN>
 
Upvote 0
motilulla said:
Excellant! Akuini, with your code I got the results perfect! </SPAN></SPAN>

Thank you for your help</SPAN></SPAN>

Kind Regards</SPAN></SPAN>
Moti :)</SPAN></SPAN>
Click to expand...
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</SPAN></SPAN>
Code: 
 Do While va(i + 1, 1) = 0
</SPAN></SPAN>
Please can you check it?</SPAN></SPAN>

Thank you

Kind Regards</SPAN></SPAN>
Moti</SPAN></SPAN>
 
Upvote 0
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
Click to expand...

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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Selenium VBA - loop to align new data with old data in worksheet
Replies
8
Views
349
Anthony47
A
G
  • Solved
Golf Handicap Formula help
2
Replies
11
Views
550
Gulfcourse
G
E
  • Solved
extend Excel formula to filter based on a period
Replies
5
Views
274
smozgur
smozgur
E
  • Locked
  • Question Question
Excel formjlailter data to be analysed
Replies
1
Views
150
Fluff
Fluff
M
  • Solved
Find quads delay (Loop quads within 5 n1:n5 results)
Replies
6
Views
229
motilulla
M

Forum statistics

Threads
1,223,886
Messages
6,175,198
Members
452,617
Latest member
Narendra Babu D

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top