Need formula or VBA count the delays

M

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,362
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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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

B
  • Solved
vba code to give next Sunday date for pasted data
Replies
2
Views
213
bh24524
B
J
  • Solved
Calculate last 5 soccer team goals using data from another sheet on same workbook.
2
Replies
19
Views
965
felixstraube
F
M
  • Solved
Find Delay…
2
Replies
11
Views
477
Anthony47
A
MrDB4Excel
  • Solved
Disregard Negative Results
Replies
3
Views
91
Joe4
Joe4
Q
  • Solved
Calculating total number of hours within a period, counting only specific days of the week, and specific time intervals.
Replies
8
Views
653
Dave Patton
D

Forum statistics

Threads
1,215,523
Messages
6,125,317
Members
449,218
Latest member
Excel Master

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