help to calculate average

baqer · Jun 1, 2022

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	D	E	F
1	T	00:11.0		Time	Average
2	L	854
3	L	846	Time Between	00:11:00 To 00:26:00	851.06
4	L	841	Time Between	00: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

offthelip · Jun 1, 2022

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

baqer · Jun 2, 2022

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

baqer · Jun 2, 2022

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