help to calculate average

B

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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
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: 6
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
A​
B​
C​
D​
E​
F​
G​
H​
1​
T
2/23/2022 0:00​
2​
L
854​
TimeBetween44615.0001273148 TO 44615.0003009259
851.06​
3​
L
846​
TimeBetween44615.0003009259 TO 44615.0005324074
852.59​
4​
L
841​
TimeBetween44615.0005324074 TO 44615.0007638889
851.5​
5​
L
833​
TimeBetween44615.0007638889 TO 44615.0009953704
850.54​
6​
L
831​
TimeBetween44615.0009953704 TO 44615.0012268519
852.03​
7​
L
829​
TimeBetween44615.0012268519 TO 44615.0014583333
856.69​
8​
L
831​
TimeBetween44615.0014583333 TO 44615.0016898148
856.63​
9​
L
830​
TimeBetween44615.0016898148 TO 44615.0019212963
855.89​
10​
L
828​
TimeBetween44615.0019212963 TO 44615.0021527778
855.22​
11​
L
832​
TimeBetween44615.0021527778 TO 44615.0023842593
854.2​
12​
L
835​
TimeBetween44615.0023842593 TO 44615.0026157407
856.32​
13​
L
842​
TimeBetween44615.0026157407 TO 44615.0028472222
859.37​
14​
L
847​
TimeBetween44615.0028472222 TO 44615.0030787037
856.38​
15​
L
852​
TimeBetween44615.0030787037 TO 44615.0033101852
849.47​
16​
L
858​
TimeBetween44615.0033101852 TO 44615.0035416667
841.92​
17​
L
861​
TimeBetween44615.0035416667 TO 44615.0037731481
840.22​
18​
L
864​
TimeBetween44615.0037731481 TO 44615.0040046296
834.33​
19​
L
868​
TimeBetween44615.0040046296 TO 44615.0042361111
829.04​
20​
L
871​
TimeBetween44615.0042361111 TO 44615.0044675926
822.46​
21​
L
869​
TimeBetween44615.0044675926 TO 44615.0046990741
817.33​
22​
L
865​
TimeBetween44615.0046990741 TO 44615.0049305556
813.84​
23​
L
864​
TimeBetween44615.0049305556 TO 44615.005162037
814.4​
24​
L
863​
TimeBetween44615.005162037 TO 44615.0053935185
811.05​
25​
L
860​
TimeBetween44615.0053935185 TO 44615.005625
812.23​
26​
L
856​
TimeBetween44615.005625 TO 44615.0058564815
811.7​
27​
L
853​
TimeBetween44615.0058564815 TO 44615.006087963
813.03​
28​
L
850​
TimeBetween44615.006087963 TO 44615.0063194444
819.43​
29​
L
841​
TimeBetween44615.0063194444 TO 44615.0065509259
829.06​
30​
L
839​
TimeBetween44615.0065509259 TO 44615.0067824074
830.91​
31​
L
836​
TimeBetween44615.0067824074 TO 44615.0070138889
834.54​
32​
L
836​
TimeBetween44615.0070138889 TO 44615.0072453704
840.37​
33​
L
839​
TimeBetween44615.0072453704 TO 44615.0074768519
844.54​
34​
L
842​
TimeBetween44615.0074768519 TO 44615.0077083333
850.06​
35​
L
843​
TimeBetween44615.0077083333 TO 44615.0079398148
852.35​
36​
L
844​
TimeBetween44615.0079398148 TO 44615.0081712963
848.88​
37​
L
846​
TimeBetween44615.0081712963 TO 44615.0084027778
845.59​
38​
L
847​
TimeBetween44615.0084027778 TO 44615.0086342593
847.89​
39​
L
851​
TimeBetween44615.0086342593 TO 44615.0088657407
842.69​
40​
L
856​
TimeBetween44615.0088657407 TO 44615.0090972222
844.11​
41​
L
860​
TimeBetween44615.0090972222 TO 44615.0093287037
838.36​
42​
L
863​
TimeBetween44615.0093287037 TO 44615.0095601852
832.68​
43​
L
861​
TimeBetween44615.0095601852 TO 44615.0097916667
833.37​
44​
L
860​
TimeBetween44615.0097916667 TO 44615.0100231481
828.47​
45​
L
859​
TimeBetween44615.0100231481 TO 44615.0102546296
828.04​
46​
L
855​
TimeBetween44615.0102546296 TO 44615.0104861111
827.47​
47​
L
853​
TimeBetween44615.0104861111 TO 44615.0107175926
822.88​
48​
L
851​
TimeBetween44615.0107175926 TO 44615.0109490741
824.87​
49​
L
853​
TimeBetween44615.0109490741 TO 44615.0111805556
825.95​
50​
L
850​
TimeBetween44615.0111805556 TO 44615.011412037
823.82​
51​
L
853​
TimeBetween44615.011412037 TO 44615.0116435185
827.08​
52​
L
853​
TimeBetween44615.0116435185 TO 44615.011875
826.41​
53​
L
854​
TimeBetween44615.011875 TO 44615.0121064815
825.56​
54​
L
855​
TimeBetween44615.0121064815 TO 44615.012337963
827.48​
55​
L
852​
TimeBetween44615.012337963 TO 44615.0125694444
823.14​
56​
L
850​
TimeBetween44615.0125694444 TO 44615.0128009259
820.83​
57​
L
847​
TimeBetween44615.0128009259 TO 44615.0130324074
819.26​
58​
L
848​
TimeBetween44615.0130324074 TO 44615.0132638889
813.57​
59​
L
854​
TimeBetween44615.0132638889 TO 44615.0134953704
813.76​
60​
L
853​
TimeBetween44615.0134953704 TO 44615.0137268519
810.95​
61​
L
856​
TimeBetween44615.0137268519 TO 44615.0139583333
805.7​
62​
L
855​
TimeBetween44615.0139583333 TO 44615.0141898148
803.36​
63​
L
852​
TimeBetween44615.0141898148 TO 44615.0144212963
801.51​
64​
L
849​
TimeBetween44615.0144212963 TO 44615.0146527778
797.67​
65​
L
850​
TimeBetween44615.0146527778 TO 44615.0148842593
804.51​
66​
L
853​
TimeBetween44615.0148842593 TO 44615.0151157407
799.47​
67​
L
853​
TimeBetween44615.0151157407 TO 44615.0153472222
803.05​
68​
L
856​
TimeBetween44615.0153472222 TO 44615.0155787037
804.69​
69​
L
853​
TimeBetween44615.0155787037 TO 44615.0158101852
803.67​
70​
L
852​
TimeBetween44615.0158101852 TO 44615.0160416667
810.88​
71​
L
847​
TimeBetween44615.0160416667 TO 44615.0162731481
811.82​
72​
L
844​
TimeBetween44615.0162731481 TO 44615.0165046296
810.62​
73​
L
844​
TimeBetween44615.0165046296 TO 44615.0167361111
813.28​
74​
L
849​
TimeBetween44615.0167361111 TO 44615.0169675926
815.51​
75​
L
853​
TimeBetween44615.0169675926 TO 44615.0171990741
817.29​
76​
L
858​
TimeBetween44615.0171990741 TO 44615.0174305556
819.7​
77​
L
855​
TimeBetween44615.0174305556 TO 44615.017662037
815.52​
78​
L
859​
TimeBetween44615.017662037 TO 44615.0178935185
815​
79​
L
858​
TimeBetween44615.0178935185 TO 44615.018125
814.98​
80​
L
862​
TimeBetween44615.018125 TO 44615.0183564815
811.69​
81​
L
863​
TimeBetween44615.0183564815 TO 44615.018587963
812.64​
82​
L
863​
TimeBetween44615.018587963 TO 44615.0188194444
808.87​
83​
L
862​
TimeBetween44615.0188194444 TO 44615.0190509259
806.37​
84​
L
859​
TimeBetween44615.0190509259 TO 44615.0192824074
804.99​
85​
L
859​
TimeBetween44615.0192824074 TO 44615.0195138889
803.51​
86​
L
857​
TimeBetween44615.0195138889 TO 44615.0197453704
800.97​
87​
L
856​
TimeBetween44615.0197453704 TO 44615.0199768519
798.76​
88​
L
849​
TimeBetween44615.0199768519 TO 44615.0202083333
797.29​
89​
L
848​
TimeBetween44615.0202083333 TO 44615.0204398148
793.45​
90​
L
848​
TimeBetween44615.0204398148 TO 44615.0206712963
791.95​
91​
L
850​
TimeBetween44615.0206712963 TO 44615.0209027778
789.49​
92​
L
847​
TimeBetween44615.0209027778 TO 44615.0211342593
789.39​
93​
L
848​
TimeBetween44615.0211342593 TO 44615.0213657407
789.19​
94​
L
844​
TimeBetween44615.0213657407 TO 44615.0215972222
787.53​
95​
L
845​
TimeBetween44615.0215972222 TO 44615.0218287037
787.18​
96​
L
844​
TimeBetween44615.0218287037 TO 44615.0220601852
787.48​
97​
L
848​
TimeBetween44615.0220601852 TO 44615.0222916667
789.53​
98​
L
853​
TimeBetween44615.0222916667 TO 44615.0225231481
791.79​
99​
L
855​
TimeBetween44615.0225231481 TO 44615.0227546296
790.08​
100​
L
858​
TimeBetween44615.0227546296 TO 44615.0229976852
793.26​
101​
L
855​
TimeBetween44615.0229976852 TO 44615.0232291667
793.12​
102​
T
2/23/2022 0:00​
TimeBetween44615.0232291667 TO 44615.0234490741
796​
103​
L
858​
TimeBetween44615.0234490741 TO 44615.0236805556
798.36​
104​
L
856​
TimeBetween44615.0236805556 TO 44615.023912037
794.04​
105​
L
854​
TimeBetween44615.023912037 TO 44615.0241435185
795.55​
Sheet: STA-20220223
 
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
offthelip said:
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
Click to expand...
thank you very mach best regards
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
average based on time
Replies
1
Views
297
baqer
B
Romano_odK
  • Solved
Counting duplicate values in column
Replies
8
Views
257
Peter_SSs
Peter_SSs
earthworm
  • Question
Split Data into separate worksheet based on Cell values
Replies
8
Views
457
earthworm
earthworm
D
  • Question
Excel USPS Zip Code Chart
Replies
3
Views
253
Scott Huish
Scott Huish
J
  • Question
Need some help with permutation code, convert from one cell to a range
Replies
3
Views
284
johnnyL
J

Forum statistics

Threads
1,216,122
Messages
6,128,963
Members
449,480
Latest member
yesitisasport

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