help to calculate average

baqer

New Member
Joined
May 12, 2021
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
hello all moderator from mrexcel
i have file like this below an i am need VBA code to calculate average each cell in range "B" if it between "T" in range "A"
like look this sample can any help me to calculate average range B if data between "T" in range "A" the "T" is time
thank you very Mach and my file is 452288 row

Excel 2016 (Windows) 64 bit

A
B
C
D
E
F
G
H
1
T
00:11.0​
TimeAverage
2
L
854​
3
L
846​
Time Between00:11:00 To 00:26:00
851.06​
4
L
841​
Time Between00:26:00 To 00:46:00
852.59​
5
L
833​
6
L
831​
7
L
829​
8
L
831​
9
L
830​
10
L
828​
11
L
832​
12
L
835​
13
L
842​
14
L
847​
15
L
852​
16
L
858​
17
L
861​
18
L
864​
19
L
868​
20
L
871​
21
L
869​
22
L
865​
23
L
864​
24
L
863​
25
L
860​
26
L
856​
27
L
853​
28
L
850​
29
L
841​
30
L
839​
31
L
836​
32
L
836​
33
L
839​
34
L
842​
35
L
843​
36
L
844​
37
L
846​
38
L
847​
39
L
851​
40
L
856​
41
L
860​
42
L
863​
43
L
861​
44
L
860​
45
L
859​
46
L
855​
47
L
853​
48
L
851​
49
L
853​
50
L
850​
51
L
853​
52
L
853​
53
L
854​
54
L
855​
55
L
852​
56
L
850​
57
L
847​
58
L
848​
59
L
854​
60
L
853​
61
L
856​
62
L
855​
63
L
852​
64
L
849​
65
L
850​
66
L
853​
67
L
853​
68
L
856​
69
L
853​
70
L
852​
71
L
847​
72
L
844​
73
L
844​
74
L
849​
75
L
853​
76
L
858​
77
L
855​
78
L
859​
79
L
858​
80
L
862​
81
L
863​
82
L
863​
83
L
862​
84
L
859​
85
L
859​
86
L
857​
87
L
856​
88
L
849​
89
L
848​
90
L
848​
91
L
850​
92
L
847​
93
L
848​
94
L
844​
95
L
845​
96
L
844​
97
L
848​
98
L
853​
99
L
855​
100
L
858​
101
L
855​
102
T
00:26.0​
103
L
858​
104
L
856​
105
L
854​
106
L
857​

Sheet: STA-20220223
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
try this:
VBA Code:
Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 2))
Dim Temparr(1 To 1, 1 To 3)
tx = 2
cnt = 0
sm = 0
titl = ""
Start = False
For i = 1 To lastrow
 If inarr(i, 1) = "T" Then
  If Start And cnt > 0 Then
    av = sm / cnt
    cnt = 0
    sm = 0
    titl = titl & inarr(i, 2)
    Temparr(1, 1) = "TimeBetween"
    Temparr(1, 2) = titl
    Temparr(1, 3) = av
    Range(Cells(tx, 4), Cells(tx, 6)) = Temparr
    tx = tx + 1
    titl = inarr(i, 2) & " To "
  Else
   titl = inarr(i, 2) & " To "
   Start = True
  End If
 Else
  cnt = cnt + 1
  sm = sm + inarr(i, 2)
 End If
Next i
End Sub
 
Upvote 0
thank you very Mach for your help
but i have tow problem
if i klick on the cell time is showing like cell "A1"
the time is showing Other format in result
and end of cod i have one error like Picture on yellow mark i can attach original text file
on the file have to items that "PS" and "C" Which I do not need and should be removed or ignored
 

Attachments

  • excel.png
    excel.png
    148.3 KB · Views: 27
Upvote 0
this is the original file and result excel whit vab cod
T=02/23/2022 00:00:11.000 0
L=854
L=846
L=841
L=833
L=831
L=829
L=831
L=830
L=828
L=832
L=835
L=842
L=847
L=852
L=858
L=861
L=864
L=868
L=871
L=869
L=865
L=864
L=863
L=860
L=856
L=853
L=850
L=841
L=839
L=836
L=836
L=839
L=842
L=843
L=844
L=846
L=847
L=851
L=856
L=860
L=863
L=861
L=860
L=859
L=855
L=853
L=851
L=853
L=850
L=853
L=853
L=854
L=855
L=852
L=850
L=847
L=848
L=854
L=853
L=856
L=855
L=852
L=849
L=850
L=853
L=853
L=856
L=853
L=852
L=847
L=844
L=844
L=849
L=853
L=858
L=855
L=859
L=858
L=862
L=863
L=863
L=862
L=859
L=859
L=857
L=856
L=849
L=848
L=848
L=850
L=847
L=848
L=844
L=845
L=844
L=848
L=853
L=855
L=858
L=855
PS=200,1369,NITB,6800,3,0,1,3600,7200,0,0
C=34004
T=02/23/2022 00:00:26.000 0
L=858
L=856
L=854
L=857
L=857
L=860
L=860
L=855
L=852
L=847
L=846
L=846
L=849
L=853
L=856
L=857
L=858
L=855
L=856
L=852
L=852
L=849
L=847
L=848
L=848
L=847
L=850
L=848
L=847
L=845
L=846
L=848
L=849
L=852
L=856
L=858
L=858
L=859
L=860
L=861
L=863
L=861
L=859
L=854
L=852
L=847
L=847
L=844
L=847
L=846
L=849
L=846
L=845
L=846
L=847
L=848
L=852
L=854
L=857
L=855
L=857
L=853
L=854
L=853
L=856
L=861
L=861
L=856
L=854
L=847
L=843
L=840
L=838
L=841
L=845
L=846
L=845
L=851
L=858
L=863
L=867
L=867
L=867
L=869
L=863
L=858
L=850
L=847
L=842
L=845
L=846
L=848
L=844
L=847
L=851
L=852
L=857
L=860
L=863
L=863
PS=200,1369,NITB,6800,3,0,1,3600,7200,0,0
C=34008
T=02/23/2022 00:00:46.000 0
L=860
L=860
L=855
L=855
L=855
L=857
L=854
L=855
L=852
L=846
L=845
L=844
L=846
L=849
L=851
L=854
L=851
L=855
L=853
L=855
L=860
L=860
L=860
L=861
L=859
L=859
L=855
L=857
L=854
L=854
L=851
L=850
L=852
L=845
L=846
L=846
L=844
L=844
L=845
L=843
L=848
L=849
L=850
L=855
L=857
L=860
L=861
L=861
L=857
L=856
L=856
L=854
L=851
L=850
L=849
L=848
L=850
L=843
L=842
L=836
L=838
L=843
L=849
L=850
L=852
L=850
L=853
L=854
L=854
L=855
L=858
L=856
L=854
L=852
L=851
L=850
L=851
L=853
L=853
L=848
L=846
L=846
L=849
L=850
L=852
L=853
L=854
L=853
L=848
L=848
L=848
L=849
L=852
L=854
L=853
L=850
L=847
L=845
L=849
L=850
PS=200,1369,NITB,6800,3,0,1,3600,7200,0,0
C=34004
T=02/23/2022 00:01:06.000 0
L=853
L=856
L=856
L=854
L=852
L=851
L=853
L=852
L=851
L=851
L=844
L=840
L=837
L=838
L=841
L=846
L=849
L=848
L=852
L=852
L=855
L=862
L=863
L=864
L=861
L=860
L=855
L=853
L=850
L=847
L=847
L=843
L=836
L=835
L=832
L=832
L=838
L=843
L=847
L=852
L=857
L=863
L=866
L=870
L=872
L=874
L=874
L=869
L=864
L=856
L=845
L=837
L=827
L=822
L=821
L=823
L=826
L=833
L=837
L=844
L=851
L=858
L=865
L=872
L=879
L=878
L=882
L=877
L=871
L=865
L=857
L=847
L=842
L=834
L=830
L=827
L=825
L=823
L=827
L=831
L=839
L=850
L=858
L=866
L=872
L=878
L=879
L=878
L=874
L=867
L=861
L=856
L=850
L=841
L=842
L=836
L=835
L=832
L=832
L=836
PS=200,1369,NITB,6800,3,0,1,3600,7200,0,0
C=34003
T=02/23/2022 00:01:26.000 0
L=841
L=846
L=853
L=854
L=857
L=857
L=859
L=862
L=865
L=865
L=862
L=859
L=858
L=853
L=852
L=850
L=850
L=848
L=848
L=848
L=846
L=843
L=842
L=838
L=840
L=845
L=848
L=859
L=862
L=865
L=865
L=864
L=861
L=860
L=860
L=857
L=859
L=853
L=849
L=841
L=836
L=835
L=834
L=837
L=840
L=841
L=843
L=844
L=848
L=850
L=855
L=858
L=864
L=865
L=864
L=865
L=863
L=861
L=857
L=851
L=849
L=843
L=845
L=846
L=842
L=841
L=838
L=840
L=839
L=845
L=847
L=854
L=855
L=857
L=857
L=853
L=853
L=857
L=859
L=864
L=864
L=865
L=856
L=859
L=854
L=857
L=856
L=857
L=858
L=857
L=852
L=849
L=848
L=846
L=846
L=848
L=849
L=844
L=849
L=850
PS=200,1369,NITB,6800,3,0,1,3600,7200,0,0
C=34008
T=02/23/2022 00:01:46.000 0
L=854
L=856
L=858
L=859



Excel 2016 (Windows) 64 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A
[/th][th]
B
[/th][th]
C
[/th][th]
D
[/th][th]
E
[/th][th]
F
[/th][th]
G
[/th][th]
H
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1
[/td][td]T[/td][td]
2/23/2022 0:00​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2
[/td][td]L[/td][td]
854​
[/td][td][/td][td]TimeBetween[/td][td]44615.0001273148 TO 44615.0003009259[/td][td]
851.06​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3
[/td][td]L[/td][td]
846​
[/td][td][/td][td]TimeBetween[/td][td]44615.0003009259 TO 44615.0005324074[/td][td]
852.59​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4
[/td][td]L[/td][td]
841​
[/td][td][/td][td]TimeBetween[/td][td]44615.0005324074 TO 44615.0007638889[/td][td]
851.5​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5
[/td][td]L[/td][td]
833​
[/td][td][/td][td]TimeBetween[/td][td]44615.0007638889 TO 44615.0009953704[/td][td]
850.54​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6
[/td][td]L[/td][td]
831​
[/td][td][/td][td]TimeBetween[/td][td]44615.0009953704 TO 44615.0012268519[/td][td]
852.03​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7
[/td][td]L[/td][td]
829​
[/td][td][/td][td]TimeBetween[/td][td]44615.0012268519 TO 44615.0014583333[/td][td]
856.69​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8
[/td][td]L[/td][td]
831​
[/td][td][/td][td]TimeBetween[/td][td]44615.0014583333 TO 44615.0016898148[/td][td]
856.63​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9
[/td][td]L[/td][td]
830​
[/td][td][/td][td]TimeBetween[/td][td]44615.0016898148 TO 44615.0019212963[/td][td]
855.89​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10
[/td][td]L[/td][td]
828​
[/td][td][/td][td]TimeBetween[/td][td]44615.0019212963 TO 44615.0021527778[/td][td]
855.22​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11
[/td][td]L[/td][td]
832​
[/td][td][/td][td]TimeBetween[/td][td]44615.0021527778 TO 44615.0023842593[/td][td]
854.2​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12
[/td][td]L[/td][td]
835​
[/td][td][/td][td]TimeBetween[/td][td]44615.0023842593 TO 44615.0026157407[/td][td]
856.32​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13
[/td][td]L[/td][td]
842​
[/td][td][/td][td]TimeBetween[/td][td]44615.0026157407 TO 44615.0028472222[/td][td]
859.37​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14
[/td][td]L[/td][td]
847​
[/td][td][/td][td]TimeBetween[/td][td]44615.0028472222 TO 44615.0030787037[/td][td]
856.38​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15
[/td][td]L[/td][td]
852​
[/td][td][/td][td]TimeBetween[/td][td]44615.0030787037 TO 44615.0033101852[/td][td]
849.47​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16
[/td][td]L[/td][td]
858​
[/td][td][/td][td]TimeBetween[/td][td]44615.0033101852 TO 44615.0035416667[/td][td]
841.92​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
17
[/td][td]L[/td][td]
861​
[/td][td][/td][td]TimeBetween[/td][td]44615.0035416667 TO 44615.0037731481[/td][td]
840.22​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
18
[/td][td]L[/td][td]
864​
[/td][td][/td][td]TimeBetween[/td][td]44615.0037731481 TO 44615.0040046296[/td][td]
834.33​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
19
[/td][td]L[/td][td]
868​
[/td][td][/td][td]TimeBetween[/td][td]44615.0040046296 TO 44615.0042361111[/td][td]
829.04​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
20
[/td][td]L[/td][td]
871​
[/td][td][/td][td]TimeBetween[/td][td]44615.0042361111 TO 44615.0044675926[/td][td]
822.46​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
21
[/td][td]L[/td][td]
869​
[/td][td][/td][td]TimeBetween[/td][td]44615.0044675926 TO 44615.0046990741[/td][td]
817.33​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
22
[/td][td]L[/td][td]
865​
[/td][td][/td][td]TimeBetween[/td][td]44615.0046990741 TO 44615.0049305556[/td][td]
813.84​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
23
[/td][td]L[/td][td]
864​
[/td][td][/td][td]TimeBetween[/td][td]44615.0049305556 TO 44615.005162037[/td][td]
814.4​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
24
[/td][td]L[/td][td]
863​
[/td][td][/td][td]TimeBetween[/td][td]44615.005162037 TO 44615.0053935185[/td][td]
811.05​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
25
[/td][td]L[/td][td]
860​
[/td][td][/td][td]TimeBetween[/td][td]44615.0053935185 TO 44615.005625[/td][td]
812.23​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
26
[/td][td]L[/td][td]
856​
[/td][td][/td][td]TimeBetween[/td][td]44615.005625 TO 44615.0058564815[/td][td]
811.7​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
27
[/td][td]L[/td][td]
853​
[/td][td][/td][td]TimeBetween[/td][td]44615.0058564815 TO 44615.006087963[/td][td]
813.03​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
28
[/td][td]L[/td][td]
850​
[/td][td][/td][td]TimeBetween[/td][td]44615.006087963 TO 44615.0063194444[/td][td]
819.43​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
29
[/td][td]L[/td][td]
841​
[/td][td][/td][td]TimeBetween[/td][td]44615.0063194444 TO 44615.0065509259[/td][td]
829.06​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
30
[/td][td]L[/td][td]
839​
[/td][td][/td][td]TimeBetween[/td][td]44615.0065509259 TO 44615.0067824074[/td][td]
830.91​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
31
[/td][td]L[/td][td]
836​
[/td][td][/td][td]TimeBetween[/td][td]44615.0067824074 TO 44615.0070138889[/td][td]
834.54​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
32
[/td][td]L[/td][td]
836​
[/td][td][/td][td]TimeBetween[/td][td]44615.0070138889 TO 44615.0072453704[/td][td]
840.37​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
33
[/td][td]L[/td][td]
839​
[/td][td][/td][td]TimeBetween[/td][td]44615.0072453704 TO 44615.0074768519[/td][td]
844.54​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
34
[/td][td]L[/td][td]
842​
[/td][td][/td][td]TimeBetween[/td][td]44615.0074768519 TO 44615.0077083333[/td][td]
850.06​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
35
[/td][td]L[/td][td]
843​
[/td][td][/td][td]TimeBetween[/td][td]44615.0077083333 TO 44615.0079398148[/td][td]
852.35​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
36
[/td][td]L[/td][td]
844​
[/td][td][/td][td]TimeBetween[/td][td]44615.0079398148 TO 44615.0081712963[/td][td]
848.88​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
37
[/td][td]L[/td][td]
846​
[/td][td][/td][td]TimeBetween[/td][td]44615.0081712963 TO 44615.0084027778[/td][td]
845.59​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
38
[/td][td]L[/td][td]
847​
[/td][td][/td][td]TimeBetween[/td][td]44615.0084027778 TO 44615.0086342593[/td][td]
847.89​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
39
[/td][td]L[/td][td]
851​
[/td][td][/td][td]TimeBetween[/td][td]44615.0086342593 TO 44615.0088657407[/td][td]
842.69​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
40
[/td][td]L[/td][td]
856​
[/td][td][/td][td]TimeBetween[/td][td]44615.0088657407 TO 44615.0090972222[/td][td]
844.11​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
41
[/td][td]L[/td][td]
860​
[/td][td][/td][td]TimeBetween[/td][td]44615.0090972222 TO 44615.0093287037[/td][td]
838.36​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
42
[/td][td]L[/td][td]
863​
[/td][td][/td][td]TimeBetween[/td][td]44615.0093287037 TO 44615.0095601852[/td][td]
832.68​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
43
[/td][td]L[/td][td]
861​
[/td][td][/td][td]TimeBetween[/td][td]44615.0095601852 TO 44615.0097916667[/td][td]
833.37​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
44
[/td][td]L[/td][td]
860​
[/td][td][/td][td]TimeBetween[/td][td]44615.0097916667 TO 44615.0100231481[/td][td]
828.47​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
45
[/td][td]L[/td][td]
859​
[/td][td][/td][td]TimeBetween[/td][td]44615.0100231481 TO 44615.0102546296[/td][td]
828.04​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
46
[/td][td]L[/td][td]
855​
[/td][td][/td][td]TimeBetween[/td][td]44615.0102546296 TO 44615.0104861111[/td][td]
827.47​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
47
[/td][td]L[/td][td]
853​
[/td][td][/td][td]TimeBetween[/td][td]44615.0104861111 TO 44615.0107175926[/td][td]
822.88​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
48
[/td][td]L[/td][td]
851​
[/td][td][/td][td]TimeBetween[/td][td]44615.0107175926 TO 44615.0109490741[/td][td]
824.87​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
49
[/td][td]L[/td][td]
853​
[/td][td][/td][td]TimeBetween[/td][td]44615.0109490741 TO 44615.0111805556[/td][td]
825.95​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
50
[/td][td]L[/td][td]
850​
[/td][td][/td][td]TimeBetween[/td][td]44615.0111805556 TO 44615.011412037[/td][td]
823.82​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
51
[/td][td]L[/td][td]
853​
[/td][td][/td][td]TimeBetween[/td][td]44615.011412037 TO 44615.0116435185[/td][td]
827.08​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
52
[/td][td]L[/td][td]
853​
[/td][td][/td][td]TimeBetween[/td][td]44615.0116435185 TO 44615.011875[/td][td]
826.41​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
53
[/td][td]L[/td][td]
854​
[/td][td][/td][td]TimeBetween[/td][td]44615.011875 TO 44615.0121064815[/td][td]
825.56​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
54
[/td][td]L[/td][td]
855​
[/td][td][/td][td]TimeBetween[/td][td]44615.0121064815 TO 44615.012337963[/td][td]
827.48​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
55
[/td][td]L[/td][td]
852​
[/td][td][/td][td]TimeBetween[/td][td]44615.012337963 TO 44615.0125694444[/td][td]
823.14​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
56
[/td][td]L[/td][td]
850​
[/td][td][/td][td]TimeBetween[/td][td]44615.0125694444 TO 44615.0128009259[/td][td]
820.83​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
57
[/td][td]L[/td][td]
847​
[/td][td][/td][td]TimeBetween[/td][td]44615.0128009259 TO 44615.0130324074[/td][td]
819.26​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
58
[/td][td]L[/td][td]
848​
[/td][td][/td][td]TimeBetween[/td][td]44615.0130324074 TO 44615.0132638889[/td][td]
813.57​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
59
[/td][td]L[/td][td]
854​
[/td][td][/td][td]TimeBetween[/td][td]44615.0132638889 TO 44615.0134953704[/td][td]
813.76​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
60
[/td][td]L[/td][td]
853​
[/td][td][/td][td]TimeBetween[/td][td]44615.0134953704 TO 44615.0137268519[/td][td]
810.95​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
61
[/td][td]L[/td][td]
856​
[/td][td][/td][td]TimeBetween[/td][td]44615.0137268519 TO 44615.0139583333[/td][td]
805.7​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
62
[/td][td]L[/td][td]
855​
[/td][td][/td][td]TimeBetween[/td][td]44615.0139583333 TO 44615.0141898148[/td][td]
803.36​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
63
[/td][td]L[/td][td]
852​
[/td][td][/td][td]TimeBetween[/td][td]44615.0141898148 TO 44615.0144212963[/td][td]
801.51​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
64
[/td][td]L[/td][td]
849​
[/td][td][/td][td]TimeBetween[/td][td]44615.0144212963 TO 44615.0146527778[/td][td]
797.67​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
65
[/td][td]L[/td][td]
850​
[/td][td][/td][td]TimeBetween[/td][td]44615.0146527778 TO 44615.0148842593[/td][td]
804.51​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
66
[/td][td]L[/td][td]
853​
[/td][td][/td][td]TimeBetween[/td][td]44615.0148842593 TO 44615.0151157407[/td][td]
799.47​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
67
[/td][td]L[/td][td]
853​
[/td][td][/td][td]TimeBetween[/td][td]44615.0151157407 TO 44615.0153472222[/td][td]
803.05​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
68
[/td][td]L[/td][td]
856​
[/td][td][/td][td]TimeBetween[/td][td]44615.0153472222 TO 44615.0155787037[/td][td]
804.69​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
69
[/td][td]L[/td][td]
853​
[/td][td][/td][td]TimeBetween[/td][td]44615.0155787037 TO 44615.0158101852[/td][td]
803.67​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
70
[/td][td]L[/td][td]
852​
[/td][td][/td][td]TimeBetween[/td][td]44615.0158101852 TO 44615.0160416667[/td][td]
810.88​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
71
[/td][td]L[/td][td]
847​
[/td][td][/td][td]TimeBetween[/td][td]44615.0160416667 TO 44615.0162731481[/td][td]
811.82​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
72
[/td][td]L[/td][td]
844​
[/td][td][/td][td]TimeBetween[/td][td]44615.0162731481 TO 44615.0165046296[/td][td]
810.62​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
73
[/td][td]L[/td][td]
844​
[/td][td][/td][td]TimeBetween[/td][td]44615.0165046296 TO 44615.0167361111[/td][td]
813.28​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
74
[/td][td]L[/td][td]
849​
[/td][td][/td][td]TimeBetween[/td][td]44615.0167361111 TO 44615.0169675926[/td][td]
815.51​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
75
[/td][td]L[/td][td]
853​
[/td][td][/td][td]TimeBetween[/td][td]44615.0169675926 TO 44615.0171990741[/td][td]
817.29​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
76
[/td][td]L[/td][td]
858​
[/td][td][/td][td]TimeBetween[/td][td]44615.0171990741 TO 44615.0174305556[/td][td]
819.7​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
77
[/td][td]L[/td][td]
855​
[/td][td][/td][td]TimeBetween[/td][td]44615.0174305556 TO 44615.017662037[/td][td]
815.52​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
78
[/td][td]L[/td][td]
859​
[/td][td][/td][td]TimeBetween[/td][td]44615.017662037 TO 44615.0178935185[/td][td]
815​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
79
[/td][td]L[/td][td]
858​
[/td][td][/td][td]TimeBetween[/td][td]44615.0178935185 TO 44615.018125[/td][td]
814.98​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
80
[/td][td]L[/td][td]
862​
[/td][td][/td][td]TimeBetween[/td][td]44615.018125 TO 44615.0183564815[/td][td]
811.69​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
81
[/td][td]L[/td][td]
863​
[/td][td][/td][td]TimeBetween[/td][td]44615.0183564815 TO 44615.018587963[/td][td]
812.64​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
82
[/td][td]L[/td][td]
863​
[/td][td][/td][td]TimeBetween[/td][td]44615.018587963 TO 44615.0188194444[/td][td]
808.87​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
83
[/td][td]L[/td][td]
862​
[/td][td][/td][td]TimeBetween[/td][td]44615.0188194444 TO 44615.0190509259[/td][td]
806.37​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
84
[/td][td]L[/td][td]
859​
[/td][td][/td][td]TimeBetween[/td][td]44615.0190509259 TO 44615.0192824074[/td][td]
804.99​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
85
[/td][td]L[/td][td]
859​
[/td][td][/td][td]TimeBetween[/td][td]44615.0192824074 TO 44615.0195138889[/td][td]
803.51​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
86
[/td][td]L[/td][td]
857​
[/td][td][/td][td]TimeBetween[/td][td]44615.0195138889 TO 44615.0197453704[/td][td]
800.97​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
87
[/td][td]L[/td][td]
856​
[/td][td][/td][td]TimeBetween[/td][td]44615.0197453704 TO 44615.0199768519[/td][td]
798.76​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
88
[/td][td]L[/td][td]
849​
[/td][td][/td][td]TimeBetween[/td][td]44615.0199768519 TO 44615.0202083333[/td][td]
797.29​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
89
[/td][td]L[/td][td]
848​
[/td][td][/td][td]TimeBetween[/td][td]44615.0202083333 TO 44615.0204398148[/td][td]
793.45​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
90
[/td][td]L[/td][td]
848​
[/td][td][/td][td]TimeBetween[/td][td]44615.0204398148 TO 44615.0206712963[/td][td]
791.95​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
91
[/td][td]L[/td][td]
850​
[/td][td][/td][td]TimeBetween[/td][td]44615.0206712963 TO 44615.0209027778[/td][td]
789.49​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
92
[/td][td]L[/td][td]
847​
[/td][td][/td][td]TimeBetween[/td][td]44615.0209027778 TO 44615.0211342593[/td][td]
789.39​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
93
[/td][td]L[/td][td]
848​
[/td][td][/td][td]TimeBetween[/td][td]44615.0211342593 TO 44615.0213657407[/td][td]
789.19​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
94
[/td][td]L[/td][td]
844​
[/td][td][/td][td]TimeBetween[/td][td]44615.0213657407 TO 44615.0215972222[/td][td]
787.53​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
95
[/td][td]L[/td][td]
845​
[/td][td][/td][td]TimeBetween[/td][td]44615.0215972222 TO 44615.0218287037[/td][td]
787.18​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
96
[/td][td]L[/td][td]
844​
[/td][td][/td][td]TimeBetween[/td][td]44615.0218287037 TO 44615.0220601852[/td][td]
787.48​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
97
[/td][td]L[/td][td]
848​
[/td][td][/td][td]TimeBetween[/td][td]44615.0220601852 TO 44615.0222916667[/td][td]
789.53​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
98
[/td][td]L[/td][td]
853​
[/td][td][/td][td]TimeBetween[/td][td]44615.0222916667 TO 44615.0225231481[/td][td]
791.79​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
99
[/td][td]L[/td][td]
855​
[/td][td][/td][td]TimeBetween[/td][td]44615.0225231481 TO 44615.0227546296[/td][td]
790.08​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
100
[/td][td]L[/td][td]
858​
[/td][td][/td][td]TimeBetween[/td][td]44615.0227546296 TO 44615.0229976852[/td][td]
793.26​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
101
[/td][td]L[/td][td]
855​
[/td][td][/td][td]TimeBetween[/td][td]44615.0229976852 TO 44615.0232291667[/td][td]
793.12​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
102
[/td][td]T[/td][td]
2/23/2022 0:00​
[/td][td][/td][td]TimeBetween[/td][td]44615.0232291667 TO 44615.0234490741[/td][td]
796​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
103
[/td][td]L[/td][td]
858​
[/td][td][/td][td]TimeBetween[/td][td]44615.0234490741 TO 44615.0236805556[/td][td]
798.36​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
104
[/td][td]L[/td][td]
856​
[/td][td][/td][td]TimeBetween[/td][td]44615.0236805556 TO 44615.023912037[/td][td]
794.04​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
105
[/td][td]L[/td][td]
854​
[/td][td][/td][td]TimeBetween[/td][td]44615.023912037 TO 44615.0241435185[/td][td]
795.55​
[/td][td][/td][td][/td][/tr]
[/table]
[Table="width:, class:grid"][tr][td]Sheet: STA-20220223[/td][/tr][/table]
 
Upvote 0
try this modification:
VBA Code:
Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 2))
Dim Temparr(1 To 1, 1 To 3)
tx = 2
cnt = 0
sm = 0
titl = ""
Start = False
For i = 1 To lastrow
 If inarr(i, 1) = "T" Then
  If Start And cnt > 0 Then
    av = sm / cnt
    cnt = 0
    sm = 0
    titl = titl & Format(inarr(i, 2), "hh:mm AM/PM")
    Temparr(1, 1) = "TimeBetween"
    Temparr(1, 2) = titl
    Temparr(1, 3) = av
    Range(Cells(tx, 4), Cells(tx, 6)) = Temparr
    tx = tx + 1
    titl = Format(inarr(i, 2), "hh:mm AM/PM") & " To "
  Else
   titl = Format(inarr(i, 2), "hh:mm AM/PM") & " To "
   Start = True
  End If
 Else
  If IsNumeric(inarr(i, 2)) Then
  cnt = cnt + 1
  sm = sm + inarr(i, 2)
  End If
 End If
Next i
End Sub
 
Upvote 0
Solution
try this modification:
VBA Code:
Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 2))
Dim Temparr(1 To 1, 1 To 3)
tx = 2
cnt = 0
sm = 0
titl = ""
Start = False
For i = 1 To lastrow
 If inarr(i, 1) = "T" Then
  If Start And cnt > 0 Then
    av = sm / cnt
    cnt = 0
    sm = 0
    titl = titl & Format(inarr(i, 2), "hh:mm AM/PM")
    Temparr(1, 1) = "TimeBetween"
    Temparr(1, 2) = titl
    Temparr(1, 3) = av
    Range(Cells(tx, 4), Cells(tx, 6)) = Temparr
    tx = tx + 1
    titl = Format(inarr(i, 2), "hh:mm AM/PM") & " To "
  Else
   titl = Format(inarr(i, 2), "hh:mm AM/PM") & " To "
   Start = True
  End If
 Else
  If IsNumeric(inarr(i, 2)) Then
  cnt = cnt + 1
  sm = sm + inarr(i, 2)
  End If
 End If
Next i
End Sub
thank you very mach best regards
 
Upvote 0

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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