Speed up slow array index calculations

[Asbestos_Jen]

I have the following setup that pulls the Average, Minimum, and Maximum velocity for each test from the raw data imported as shown from a csv file generated by the flow meter. There could be as many as 50 tests per event, and as few as 24. The “header” section of each test is always the same layout, but the number of measurements in each test varies between 20 and ~30.

My formula works, but is painfully slow. Can anyone see a way to streamline it so it runs faster? Any solution must work in Excel 2007, but I am open to VBA solutions.

Thanks!

Summary:

WIP_Hood template VAV 2024-12-20.xlsm
	A	B	C	D	E	F	G	H	I	J	K
2	Ft/min		Total # Tests:	35	Average:	72.69	Min:	0	Max:	116
3	Metric/T#	Test_001	Test_002	Test_003	Test_004	Test_005	Test_006	Test_007	Test_008	Test_009	Test_010
4	Test #	1	2	3	4	5	6	7	8	9	10
5	Average:	58.90	57.00	66.00	53.00	57.00	59.00	53.00	52.00	56.00	63.00
6	Minimum:	54.00	52.00	57.00	39.00	44.00	51.00	44.00	43.00	50.00	54.00
7	Maximum:	63.00	60.00	72.00	66.00	69.00	66.00	67.00	60.00	61.00	70.00
DataSummary

Cell Formulas
Range	Formula
D2	D2	=COUNTIF('Raw Data'!B:B,$A$5)
F2	F2	=AVERAGE($5:$5)
H2	H2	=MIN(B5:AY7)
J2	J2	=MAX(B5:AY7)
B5:K7	B5	=IFERROR(INDEX('Raw Data'!$C:$C,SMALL(IF('Raw Data'!$B:$B=$A5,ROW('Raw Data'!$B:$B)-ROW(INDEX('Raw Data'!$B:$B,1,1))+1),B$4)),$R$1)
Press CTRL+SHIFT+ENTER to enter array formulas.

Raw Data:

WIP_Hood template VAV 2024-12-20.xlsm
	A	B	C	D	E
1	LogDat2 Data File
2
3	Model Number:	9545-A
4	Serial Number:	9545A1130001
5	Test ID:	1
6	Test Abbreviation:	Test 001
7	Start Date:	30/07/2018
8	Start Time:	09:52:28
9	Duration (dd:hh:mm:ss):	0:00:00:20
10	Log Interval (mm:ss):	00:01
11	Number of points:	20
12	Notes:	Test 001
13
14	Reading Type	Standard
15		Temperature	21.1deg C
16		Pressure	29.92inHg
17
18	Statistics	Channel:	Vel	T	H
19		Units:	ft/min	deg C	%rh
20		Average:	58.9	20.3	65
21		Minimum:	54	20.3	64.8
22		Time of Minimum:	09:52:38	09:52:44	09:52:48
23		Date of Minimum:	30/07/2018	30/07/2018	30/07/2018
24		Maximum:	63	20.4	65.3
25		Time of Maximum:	09:52:30	09:52:29	09:52:29
26		Date of Maximum:	30/07/2018	30/07/2018	30/07/2018
27
28	Calibration	Meter:	18/01/2017
29	Calibration	Sensor:	Vel	T	H
30		Cal. Date	18/01/2017	18/01/2017	18/01/2017
31
32	Date	Time	Vel	T	H
33	dd/MM/yyyy	hh:mm:ss	ft/min	deg C	%rh
34	30/07/2018	09:52:29	61	20.4	65.3
35	30/07/2018	09:52:30	62	20.4	65.3
36	30/07/2018	09:52:31	61	20.4	65.3
37	30/07/2018	09:52:32	61	20.4	65.2
38	30/07/2018	09:52:33	60	20.4	65.1
39	30/07/2018	09:52:34	61	20.4	65.1
40	30/07/2018	09:52:35	55	20.4	65.1
41	30/07/2018	09:52:36	57	20.4	65
42	30/07/2018	09:52:37	55	20.4	65
43	30/07/2018	09:52:38	54	20.4	65
44	30/07/2018	09:52:39	56	20.3	65
45	30/07/2018	09:52:40	57	20.3	64.9
46	30/07/2018	09:52:41	57	20.3	64.9
47	30/07/2018	09:52:42	58	20.3	64.9
48	30/07/2018	09:52:43	58	20.3	64.9
49	30/07/2018	09:52:44	60	20.3	64.9
50	30/07/2018	09:52:45	61	20.3	64.8
51	30/07/2018	09:52:46	63	20.3	64.8
52	30/07/2018	09:52:47	62	20.3	64.8
53	30/07/2018	09:52:48	59	20.3	64.8
54	30/07/2018	09:52:49	61	20.3	64.8
55
56	Model Number:	9545-A
57	Serial Number:	9545A1130001
58	Test ID:	2
59	Test Abbreviation:	Test 002
60	Start Date:	30/07/2018
61	Start Time:	09:52:54
62	Duration (dd:hh:mm:ss):	0:00:00:20
63	Log Interval (mm:ss):	00:01
64	Number of points:	20
65	Notes:	Test 002
66
67	Reading Type	Standard
68		Temperature	21.1deg C
69		Pressure	29.92inHg
70
71	Statistics	Channel:	Vel	T	H
72		Units:	ft/min	deg C	%rh
73		Average:	57	20.2	64.8
74		Minimum:	52	20.2	64.8
75		Time of Minimum:	09:53:14	09:53:05	09:52:55
76		Date of Minimum:	30/07/2018	30/07/2018	30/07/2018
77		Maximum:	60	20.3	64.9
78		Time of Maximum:	09:53:07	09:52:55	09:53:13
79		Date of Maximum:	30/07/2018	30/07/2018	30/07/2018
80
81	Calibration	Meter:	18/01/2017
82	Calibration	Sensor:	Vel	T	H
83		Cal. Date	18/01/2017	18/01/2017	18/01/2017
84
85	Date	Time	Vel	T	H
86	dd/MM/yyyy	hh:mm:ss	ft/min	deg C	%rh
87	30/07/2018	09:52:55	55	20.3	64.8
88	30/07/2018	09:52:56	53	20.3	64.8
89	30/07/2018	09:52:57	55	20.3	64.8
90	30/07/2018	09:52:58	57	20.3	64.8
91	30/07/2018	09:52:59	55	20.3	64.8
92	30/07/2018	09:53:00	57	20.3	64.8
93	30/07/2018	09:53:01	57	20.3	64.8
94	30/07/2018	09:53:02	59	20.3	64.8
95	30/07/2018	09:53:03	55	20.3	64.8
96	30/07/2018	09:53:04	58	20.3	64.9
97	30/07/2018	09:53:05	58	20.2	64.8
98	30/07/2018	09:53:07	60	20.2	64.8
99	30/07/2018	09:53:07	60	20.2	64.8
100	30/07/2018	09:53:08	59	20.2	64.9
101	30/07/2018	09:53:09	60	20.2	64.9
102	30/07/2018	09:53:10	60	20.2	64.9
103	30/07/2018	09:53:11	58	20.2	64.9
104	30/07/2018	09:53:12	55	20.2	64.9
105	30/07/2018	09:53:13	53	20.2	64.9
106	30/07/2018	09:53:14	52	20.2	64.9
107
Raw Data

Cell Formulas
Range	Formula
C20	C20	=AVERAGE(C34:C53)
C21	C21	=MIN(C34:C53)
C24	C24	=MAX(C34:C53)

 

[Scott Huish]

The quickest way is to not use whole columns in your calculations.

 

[Asbestos_Jen]

The quickest way is to not use whole columns in your calculations.

Is there a formula to determine the last used cell/row in the data set? I could use an overestimated large row number, I guess.

 

[Scott Huish]

Is there a formula to determine the last used cell/row in the data set? I could use an overestimated large row number, I guess.

I think that's what most people do, like B1:B5000 or something