equation for forward and back

[dmheller]

so i have a table
I want to use the table to fill the 2nd table
the date at the bottom of the second table is the date to use for the first values in the daily test.
My issue is, I got it to put valves based on some equation and it filled it in but i need it to skip some data. So i made the helper sheet, its the first table. you can see there is no data in the helper sheet for 12/28/22 so i want that to skip. so i need the previous 20 data points from the date that is input(2/22/23) skipping blank dates(i made the date black in one of the first 2 data points were missing) then i need 30m data points moving forward, again skipping blanks if one occurs. Please let if you can.
thanks

41	5/25/2022	5/25/2022	0.485	0.815	180.25
42	6/1/2022				238.0667
43	6/8/2022	6/8/2022	0.8715	0.7365	220.2
44	6/16/2022	6/16/2022	0.73725	0.40725	208.3833
45	6/22/2022	6/22/2022	1.893	1.623	215.6
46	6/29/2022	6/29/2022	1.515	1.185	195.25
47	7/6/2022	7/6/2022	0.635625	0.485625	209.1771
48	7/13/2022	7/13/2022	4.629375	4.674375	162.8229
49	7/20/2022	7/20/2022	1.190625	0.845625	150.7917
50	7/27/2022	7/27/2022	1.6575	1.9875	182.1563
51	8/3/2022	8/3/2022	1.125	1.08	180.1667
52	8/10/2022	8/10/2022	1.05	1.125	182.1667
53	8/17/2022	8/17/2022	1.26	0.78	189.3333
54	8/24/2022			0.375	187.1667
55	8/31/2022	8/31/2022	3.36	3.465	195.8333
56	9/7/2022	9/7/2022	1.875	1.905	186.9167
57	9/14/2022	9/14/2022	0.285	0.36	130.5
58	9/21/2022	9/21/2022	0.36	0.105	174.25
59	9/28/2022	9/28/2022	0.76	0.61	139.3333
60	10/5/2022	10/5/2022	0.5475	0.4575	160
61	10/12/2022		0.36		169.6667
62	10/19/2022				120.5
63	10/26/2022	10/26/2022	1.311875	0.891875	191.125
64	11/2/2022	11/2/2022	0.425625	0.500625	194.7083
65	11/9/2022	11/9/2022	0.394375	0.589375	213.0625
66	11/16/2022	11/16/2022	1.470536	1.680536	176.6696
67	11/23/2022	11/23/2022	6.52	6.57	200.003
68	11/30/2022	11/30/2022	1.011429	1.311429	226.8452
69	12/7/2022	12/7/2022	0.905357	0.920357	222.503
70	12/14/2022	12/14/2022	0.350357	0.725357	218.7946
71	12/21/2022	12/21/2022	1.070357	0.935357	230.253
72	12/28/2022			1.081071	224.5923
73	1/4/2023	1/4/2023	2.343214	2.658214	217.8601
74	1/11/2023	1/11/2023	0.91875	0.99375	224.875
75	1/18/2023	1/18/2023	2.712857	2.577857	225.6488
76	1/25/2023	1/25/2023	2.573571	2.438571	170.1964
77	2/1/2023	2/1/2023	2.946429	3.291429	174.5952
78	2/8/2023	2/8/2023	2.685	1.92	255
79	2/15/2023	2/15/2023	1.41375	1.68375	230.5417
80	2/22/2023	2/22/2023	1.92	1.905	220.875
81	3/1/2023	3/1/2023	1.155	1.035	216.4167
82	3/8/2023	3/8/2023	4.925	4.91	197.4583
83	3/15/2023	3/15/2023	1.55325	1.52325	184.925
84	3/22/2023	3/22/2023	2.1555	2.6805	205.95
85	3/29/2023	3/29/2023	0.91	0.31	190.5
86	4/5/2023	4/5/2023	1.299	1.119	202.2833
87	4/12/2023	4/12/2023	0.785	0.365	241.9167
88	4/19/2023	4/19/2023	1.452	1.692	234.35
89	4/26/2023	4/26/2023	0.496	0.571	212.05
90	5/3/2023	5/3/2023	1.892	2.222	216.4333
91	5/10/2023	5/10/2023	1.54	2.215	199.8333
92	5/17/2023	5/17/2023	1.785	1.935	213.3333
93	5/24/2023	5/24/2023	1.334	1.499	211.7
94	5/31/2023	5/31/2023	1.494	1.749	212.5333
95	6/7/2023	6/7/2023	1.234	1.054	228.1167
96	6/14/2023	6/14/2023	3.875	3.695	221.25
97	6/21/2023	6/21/2023	2.523	2.718	188.4
98	6/28/2023	6/28/2023	1.805	1.55	219.4167

Preliminary Tests			Daily Tests
Test	#1	#2	Test	#1	#2	Difference
1	44867.00	0.50	1	2.16	2.68	-0.53
2	0.39	0.59	2	0.91	0.31	0.60
3	1.47	1.68	3	1.30	1.12	0.18
4	6.52	6.57	4	0.79	0.37	0.42
5	1.01	1.31	5	1.45	1.69	-0.24
6	0.91	0.92	6	0.50	0.57	-0.08
7	0.35	0.73	7	1.89	2.22	-0.33
8	1.07	0.94	8	1.54	2.22	-0.68
9	1.31	0.89	9	1.79	1.94	-0.15
10	2.34	2.66	10	1.33	1.50	-0.16
11	0.92	0.99	11	1.49	1.75	-0.26
12	2.71	2.58	12	1.23	1.05	0.18
13	2.57	2.44	13	3.88	3.70	0.18
14	2.95	3.29	14	2.52	2.72	-0.19
15	2.69	1.92	15	1.81	1.55	0.26
16	1.41	1.68	16	0.00	0.00
17	1.92	1.91	17	0.00	0.00
18	1.16	1.04	18	0.00	0.00
19	4.93	4.91	19	0.00	0.00
20	45000.00	1.52	20	0.00	0.00
STATISTICS			21	0.00	0.00
for Preliminary			22	0.00	0.00
tests			23	0.00	0.00
Std Deviation			24	0.00	0.00
of the		######	25	0.00	0.00
difference			26	0.00	0.00
UAL		######	27	0.00	0.00
UWL		######	28	0.00	0.00
LWL		######	29	0.00	0.00
LAL		######	30	0.00	0.00
date of test 1 prelim test (sample date)
#VALUE!
Date of first test (sample date)
3/22/2023

 

[dmheller]

I would like to try and explain this better.
I see the tables did nothing so i want to add mini sheets
The first sheet i am trying to fill (preliminary and daily test) i know there is data in there but prelim test 9 is red because it was an error so i had to paste different data in. So i built my helper sheet the 2nd one to try and fill it in.

I put a date in A41 3/22/23 then i managed to populate B25 but i know if the data line before 3/22/23 in the helper sheet is blank, it will also be blank. So then i wen to B24 and tried another equation and it was also incorrect. So i need the 20 previous test from 3/22/23 and to skip 12/28/23 that is where i am having issues.
then i need it to put the data from the 3/22/23 test in E6 and then keep going down and if the data is bad, i need it skipped. If i can get an equation for coulmn B and E, i can make it work for C and F as well.

I have been trying to count and use rows and index and other formulas but i cant get it to skip the blanks. please help.
thanks

DMR r11.xlsx
	A	B	C	D	E	F	G
4	Preliminary Tests			Daily Tests
5	Test	#1	#2	Test	#1	#2	Difference
6	1	0.43	0.50	1	2.16	2.68	-0.53
7	2	0.39	0.59	2	0.91	0.31	0.60
8	3	1.47	1.68	3	1.30	1.12	0.18
9	4	6.52	6.57	4	0.79	0.37	0.42
10	5	1.01	1.31	5	1.45	1.69	-0.24
11	6	0.91	0.92	6	0.50	0.57	-0.08
12	7	0.35	0.73	7	1.89	2.22	-0.33
13	8	1.07	0.94	8	1.54	2.22	-0.68
14	9	1.31	0.89	9	1.79	1.94	-0.15
15	10	2.34	2.66	10	1.33	1.50	-0.16
16	11	0.92	0.99	11	1.49	1.75	-0.26
17	12	2.71	2.58	12	1.23	1.05	0.18
18	13	2.57	2.44	13	3.88	3.70	0.18
19	14	2.95	3.29	14	2.52	2.72	-0.19
20	15	2.69	1.92	15	1.81	1.55	0.26
21	16	1.41	1.68	16	0.00	0.00
22	17	1.92	1.91	17	0.00	0.00
23	18	1.16	1.04	18	0.00	0.00
24	19	#VALUE!	4.91	19	0.00	0.00
25	20	1.55	1.52	20	0.00	0.00
26	STATISTICS			21	0.00	0.00
27	for Preliminary			22	0.00	0.00
28	tests			23	0.00	0.00
29	Std Deviation			24	0.00	0.00
30	of the		######	25	0.00	0.00
31	difference			26	0.00	0.00
32	UAL		######	27	0.00	0.00
33	UWL		######	28	0.00	0.00
34	LWL		######	29	0.00	0.00
35	LAL		######	30	0.00	0.00
36
37
38	date of test 1 prelim test (sample date)
39	#VALUE!
40	Date of first test (sample date)
41	3/22/2023
Duplicate-solution_Template

Cell Formulas
Range	Formula
B6	B6	=INDEX('bod and tss charting helper fil'!D5:H685,MATCH('Duplicate-solution_Template'!A41,'bod and tss charting helper fil'!D5:D685,0)-20,3)
C6:C13,C15:C24	C6	=INDEX(BOD!N:V,MATCH($A$41,BOD!V:V,0)-(4+($A$25-A6)*11),4)
B7:B13,B15:B23	B7	=INDEX(BOD!N:V,MATCH($A$41,BOD!V:V,0)-(4+($A$25-A7)*11),2)
E6:E35	E6	=INDEX(BOD!N:V,MATCH($A$41,BOD!V:V,0)+(7+(D6-1)*11),2)
F6:F35	F6	=INDEX(BOD!N:V,MATCH($A$41,BOD!V:V,0)+(7+(D6-1)*11),4)
G6:G35	G6	=IF(AND(E6>0,F6>0),E6-F6,"")
B24	B24	=INDEX('bod and tss charting helper fil'!D5:H685,MATCH('Duplicate-solution_Template'!A41,'bod and tss charting helper fil'!D5:D685,0)-(COUNT('bod and tss charting helper fil'!D:D)),3)
B25	B25	=INDEX('bod and tss charting helper fil'!D5:H685,MATCH('Duplicate-solution_Template'!A41,'bod and tss charting helper fil'!D5:D685,0)-1,3)
C25	C25	=INDEX(BOD!N:V,MATCH(A41,BOD!V:V,0)-(($A$25-A24)*4),4)
C30	C30	=STDEV(V6:V25)
C32	C32	=3*C30
C33	C33	=2*C30
C34	C34	=-2*C30
C35	C35	=-3*C30
A39	A39	=INDEX(BOD!T:V,MATCH($A$42,BOD!V:V)-(($A$27)*11)-8,3)

DMR r11.xlsx
	A	B	C	D	E	F	G	H
1
2	11
3
4				std	dup
5			1	8/18/2021		2.57	0	209.55
6			2	8/25/2021		0.59		161.2
7			3	9/1/2021		1.33		187.15
8			4	9/8/2021		0.59		148.2
9			5	9/15/2021		1.49		212.2
10			6	9/23/2021		0.72		193.9
11			7	9/29/2021		3.9		195.65
12			8	10/7/2021		0.54		213.35
13			9	10/13/2021		3.05		209.5
14			10	10/20/2021		0.58		213.7
15			11	10/27/2021		0.74		194.8
16			12	11/3/2021		1.2		192.55
17			13	11/10/2021		3.11		200.2
18			14	11/17/2021		1.15		215.2
19			15	11/24/2021		0.93		199.75
20			16	12/2/2021		2.233125		197.4375
21			17	12/8/2021		2.2225		210.5833
22			18	12/15/2021				200.9667
23			19	12/22/2021		2.2225		210.5833
24			20	12/29/2021		2.1225		215
25			21	1/5/2022				210.0417
26			22	1/13/2022		0.691875		219.3125
27			23	1/19/2022		0.7665		211.05
28			24	1/26/2022		1.086905		163.7917
29			25	2/2/2022				176
30			26	2/9/2022				164.1
31			27	2/17/2022				167
32			28	2/23/2022		1.647619		188.9167
33			29	3/2/2022				145.6
34			30	3/9/2022		1.8575		180.1667
35			31	3/16/2022		1.949375		171.2917
36			32	3/23/2022		1.120625		179.4271
37			33	3/30/2022		0.58625		185.2708
38			34	4/6/2022		1.184375		191.9896
39			35	4/13/2022		0.94125		195.1875
40			36	4/20/2022				218.8
41			37	4/27/2022		3.3275		187.7083
42			38	5/4/2022		1.666875		174.125
43			39	5/11/2022		0.415625		249.8125
44			40	5/18/2022		1.0925		183.9583
45			41	5/25/2022	5/25/2022	0.485	0.815	180.25
46			42	6/1/2022				238.0667
47			43	6/8/2022	6/8/2022	0.8715	0.7365	220.2
48			44	6/16/2022	6/16/2022	0.73725	0.40725	208.3833
49			45	6/22/2022	6/22/2022	1.893	1.623	215.6
50			46	6/29/2022	6/29/2022	1.515	1.185	195.25
51			47	7/6/2022	7/6/2022	0.635625	0.485625	209.1771
52			48	7/13/2022	7/13/2022	4.629375	4.674375	162.8229
53			49	7/20/2022	7/20/2022	1.190625	0.845625	150.7917
54			50	7/27/2022	7/27/2022	1.6575	1.9875	182.1563
55			51	8/3/2022	8/3/2022	1.125	1.08	180.1667
56			52	8/10/2022	8/10/2022	1.05	1.125	182.1667
57			53	8/17/2022	8/17/2022	1.26	0.78	189.3333
58			54	8/24/2022			0.375	187.1667
59			55	8/31/2022	8/31/2022	3.36	3.465	195.8333
60			56	9/7/2022	9/7/2022	1.875	1.905	186.9167
61			57	9/14/2022	9/14/2022	0.285	0.36	130.5
62			58	9/21/2022	9/21/2022	0.36	0.105	174.25
63			59	9/28/2022	9/28/2022	0.76	0.61	139.3333
64			60	10/5/2022	10/5/2022	0.5475	0.4575	160
65			61	10/12/2022		0.36		169.6667
66			62	10/19/2022				120.5
67			63	10/26/2022	10/26/2022	1.311875	0.891875	191.125
68			64	11/2/2022	11/2/2022	0.425625	0.500625	194.7083
69			65	11/9/2022	11/9/2022	0.394375	0.589375	213.0625
70			66	11/16/2022	11/16/2022	1.470536	1.680536	176.6696
71			67	11/23/2022	11/23/2022	6.52	6.57	200.003
72			68	11/30/2022	11/30/2022	1.011429	1.311429	226.8452
73			69	12/7/2022	12/7/2022	0.905357	0.920357	222.503
74			70	12/14/2022	12/14/2022	0.350357	0.725357	218.7946
75			71	12/21/2022	12/21/2022	1.070357	0.935357	230.253
76			72	12/28/2022			1.081071	224.5923
77			73	1/4/2023	1/4/2023	2.343214	2.658214	217.8601
78			74	1/11/2023	1/11/2023	0.91875	0.99375	224.875
79			75	1/18/2023	1/18/2023	2.712857	2.577857	225.6488
80			76	1/25/2023	1/25/2023	2.573571	2.438571	170.1964
81			77	2/1/2023	2/1/2023	2.946429	3.291429	174.5952
82			78	2/8/2023	2/8/2023	2.685	1.92	255
83			79	2/15/2023	2/15/2023	1.41375	1.68375	230.5417
84			80	2/22/2023	2/22/2023	1.92	1.905	220.875
85			81	3/1/2023	3/1/2023	1.155	1.035	216.4167
86			82	3/8/2023	3/8/2023	4.925	4.91	197.4583
87			83	3/15/2023	3/15/2023	1.55325	1.52325	184.925
88			84	3/22/2023	3/22/2023	2.1555	2.6805	205.95
89			85	3/29/2023	3/29/2023	0.91	0.31	190.5
90			86	4/5/2023	4/5/2023	1.299	1.119	202.2833
91			87	4/12/2023	4/12/2023	0.785	0.365	241.9167
92			88	4/19/2023	4/19/2023	1.452	1.692	234.35
93			89	4/26/2023	4/26/2023	0.496	0.571	212.05
94			90	5/3/2023	5/3/2023	1.892	2.222	216.4333
95			91	5/10/2023	5/10/2023	1.54	2.215	199.8333
96			92	5/17/2023	5/17/2023	1.785	1.935	213.3333
97			93	5/24/2023	5/24/2023	1.334	1.499	211.7
98			94	5/31/2023	5/31/2023	1.494	1.749	212.5333
99			95	6/7/2023	6/7/2023	1.234	1.054	228.1167
100			96	6/14/2023	6/14/2023	3.875	3.695	221.25
101			97	6/21/2023	6/21/2023	2.523	2.718	188.4
102			98	6/28/2023	6/28/2023	1.805	1.55	219.4167
103			99
104			100
105			101
106			102
bod and tss charting helper fil

Cell Formulas
Range	Formula
D5	D5	=INDEX(BOD!N:V,13,9)
E5	E5	=IF(AND(F5>0,G5>0),INDEX(BOD!N:V,13,9),"")
F5	F5	=INDEX(BOD!N:V,20,2)
G5	G5	=INDEX(BOD!N:V,20,4)
H5	H5	=INDEX(BOD!N:V,21,7)
D6:D102	D6	=INDEX(BOD!N:V,13+($A$2*(C6-$C$5)),9)
E6:E106	E6	=IF(AND(F6<>"",G6<>""),INDEX(BOD!N:V,13+($A$2*(C6-$C$5)),9),"")
F6:F106	F6	=IF(INDEX(BOD!N:V,20+($A$2*(C6-$C$5)),2)>0,INDEX(BOD!N:V,20+($A$2*(C6-$C$5)),2),"")
G6:G106	G6	=IF(INDEX(BOD!N:V,20+($A$2*(C6-$C$5)),4)>0,INDEX(BOD!N:V,20+($A$2*(C6-$C$5)),4),"")
H6:H102	H6	=INDEX(BOD!N:V,21+($A$2*(C6-$C$5)),7)
D103:D106	D103	=IF(AND(F103="",G103="",H103=""),"",INDEX(BOD!N:V,13+($A$2*(C103-$C$5)),9))
H103:H106	H103	=IF(INDEX(BOD!N:V,21+($A$2*(C103-$C$5)),7)=0,"",INDEX(BOD!N:V,21+($A$2*(C103-$C$5)),7))

 

[dmheller]

give this one more try with some new equations maybe someone can edit.
I now have new formulas in taht still dont work. You can see in test 9, its blank. I need that blank skipped. that blank is seen in the yellow highlights between 12-21-22 and 1-4-23.
I would also like this to skip as it fills in the first row of the daily test column if there is a blank.
Hope this helps

DMR r11.xlsx
	A	B	C	D	E	F	G	H
1
2	11
3
4				std	dup
5			1	8/18/2021		2.57	0	209.55
6			2	8/25/2021		0.59		161.2
7			3	9/1/2021		1.33		187.15
8			4	9/8/2021		0.59		148.2
9			5	9/15/2021		1.49		212.2
10			6	9/23/2021		0.72		193.9
11			7	9/29/2021		3.9		195.65
12			8	10/7/2021		0.54		213.35
13			9	10/13/2021		3.05		209.5
14			10	10/20/2021		0.58		213.7
15			11	10/27/2021		0.74		194.8
16			12	11/3/2021		1.2		192.55
17			13	11/10/2021		3.11		200.2
18			14	11/17/2021		1.15		215.2
19			15	11/24/2021		0.93		199.75
20			16	12/2/2021		2.233125		197.4375
21			17	12/8/2021		2.2225		210.5833
22			18	12/15/2021				200.9667
23			19	12/22/2021		2.2225		210.5833
24			20	12/29/2021		2.1225		215
25			21	1/5/2022				210.0417
26			22	1/13/2022		0.691875		219.3125
27			23	1/19/2022		0.7665		211.05
28			24	1/26/2022		1.086905		163.7917
29			25	2/2/2022				176
30			26	2/9/2022				164.1
31			27	2/17/2022				167
32			28	2/23/2022		1.647619		188.9167
33			29	3/2/2022				145.6
34			30	3/9/2022		1.8575		180.1667
35			31	3/16/2022		1.949375		171.2917
36			32	3/23/2022		1.120625		179.4271
37			33	3/30/2022		0.58625		185.2708
38			34	4/6/2022		1.184375		191.9896
39			35	4/13/2022		0.94125		195.1875
40			36	4/20/2022				218.8
41			37	4/27/2022		3.3275		187.7083
42			38	5/4/2022		1.666875		174.125
43			39	5/11/2022		0.415625		249.8125
44			40	5/18/2022		1.0925		183.9583
45			41	5/25/2022	5/25/2022	0.485	0.815	180.25
46			42	6/1/2022				238.0667
47			43	6/8/2022	6/8/2022	0.8715	0.7365	220.2
48			44	6/16/2022	6/16/2022	0.73725	0.40725	208.3833
49			45	6/22/2022	6/22/2022	1.893	1.623	215.6
50			46	6/29/2022	6/29/2022	1.515	1.185	195.25
51			47	7/6/2022	7/6/2022	0.635625	0.485625	209.1771
52			48	7/13/2022	7/13/2022	4.629375	4.674375	162.8229
53			49	7/20/2022	7/20/2022	1.190625	0.845625	150.7917
54			50	7/27/2022	7/27/2022	1.6575	1.9875	182.1563
55			51	8/3/2022	8/3/2022	1.125	1.08	180.1667
56			52	8/10/2022	8/10/2022	1.05	1.125	182.1667
57			53	8/17/2022	8/17/2022	1.26	0.78	189.3333
58			54	8/24/2022			0.375	187.1667
59			55	8/31/2022	8/31/2022	3.36	3.465	195.8333
60			56	9/7/2022	9/7/2022	1.875	1.905	186.9167
61			57	9/14/2022	9/14/2022	0.285	0.36	130.5
62			58	9/21/2022	9/21/2022	0.36	0.105	174.25
63			59	9/28/2022	9/28/2022	0.76	0.61	139.3333
64			60	10/5/2022	10/5/2022	0.5475	0.4575	160
65			61	10/12/2022		0.36		169.6667
66			62	10/19/2022				120.5
67			63	10/26/2022	10/26/2022	1.311875	0.891875	191.125
68			64	11/2/2022	11/2/2022	0.425625	0.500625	194.7083
69			65	11/9/2022	11/9/2022	0.394375	0.589375	213.0625
70			66	11/16/2022	11/16/2022	1.470536	1.680536	176.6696
71			67	11/23/2022	11/23/2022	6.52	6.57	200.003
72			68	11/30/2022	11/30/2022	1.011429	1.311429	226.8452
73			69	12/7/2022	12/7/2022	0.905357	0.920357	222.503
74			70	12/14/2022	12/14/2022	0.350357	0.725357	218.7946
75			71	12/21/2022	12/21/2022	1.070357	0.935357	230.253
76			72	12/28/2022			1.081071	224.5923
77			73	1/4/2023	1/4/2023	2.343214	2.658214	217.8601
78			74	1/11/2023	1/11/2023	0.91875	0.99375	224.875
79			75	1/18/2023	1/18/2023	2.712857	2.577857	225.6488
80			76	1/25/2023	1/25/2023	2.573571	2.438571	170.1964
81			77	2/1/2023	2/1/2023	2.946429	3.291429	174.5952
82			78	2/8/2023	2/8/2023	2.685	1.92	255
83			79	2/15/2023	2/15/2023	1.41375	1.68375	230.5417
84			80	2/22/2023	2/22/2023	1.92	1.905	220.875
85			81	3/1/2023	3/1/2023	1.155	1.035	216.4167
86			82	3/8/2023	3/8/2023	4.925	4.91	197.4583
87			83	3/15/2023	3/15/2023	1.55325	1.52325	184.925
88			84	3/22/2023	3/22/2023	2.1555	2.6805	205.95
89			85	3/29/2023	3/29/2023	0.91	0.31	190.5
90			86	4/5/2023	4/5/2023	1.299	1.119	202.2833
91			87	4/12/2023	4/12/2023	0.785	0.365	241.9167
92			88	4/19/2023	4/19/2023	1.452	1.692	234.35
93			89	4/26/2023	4/26/2023	0.496	0.571	212.05
94			90	5/3/2023	5/3/2023	1.892	2.222	216.4333
95			91	5/10/2023	5/10/2023	1.54	2.215	199.8333
96			92	5/17/2023	5/17/2023	1.785	1.935	213.3333
97			93	5/24/2023	5/24/2023	1.334	1.499	211.7
98			94	5/31/2023	5/31/2023	1.494	1.749	212.5333
99			95	6/7/2023	6/7/2023	1.234	1.054	228.1167
100			96	6/14/2023	6/14/2023	3.875	3.695	221.25
101			97	6/21/2023	6/21/2023	2.523	2.718	188.4
102			98	6/28/2023	6/28/2023	1.805	1.55	219.4167
103			99
104			100
105			101
106			102
bod and tss charting helper fil

Cell Formulas
Range	Formula
D5	D5	=INDEX(BOD!N:V,13,9)
E5	E5	=IF(AND(F5>0,G5>0),INDEX(BOD!N:V,13,9),"")
F5	F5	=INDEX(BOD!N:V,20,2)
G5	G5	=INDEX(BOD!N:V,20,4)
H5	H5	=INDEX(BOD!N:V,21,7)
D6:D102	D6	=INDEX(BOD!N:V,13+($A$2*(C6-$C$5)),9)
E6:E106	E6	=IF(AND(F6<>"",G6<>""),INDEX(BOD!N:V,13+($A$2*(C6-$C$5)),9),"")
F6:F106	F6	=IF(INDEX(BOD!N:V,20+($A$2*(C6-$C$5)),2)>0,INDEX(BOD!N:V,20+($A$2*(C6-$C$5)),2),"")
G6:G106	G6	=IF(INDEX(BOD!N:V,20+($A$2*(C6-$C$5)),4)>0,INDEX(BOD!N:V,20+($A$2*(C6-$C$5)),4),"")
H6:H102	H6	=INDEX(BOD!N:V,21+($A$2*(C6-$C$5)),7)
D103:D106	D103	=IF(AND(F103="",G103="",H103=""),"",INDEX(BOD!N:V,13+($A$2*(C103-$C$5)),9))
H103:H106	H103	=IF(INDEX(BOD!N:V,21+($A$2*(C103-$C$5)),7)=0,"",INDEX(BOD!N:V,21+($A$2*(C103-$C$5)),7))

DMR r11.xlsx
	A	B	C	D	E	F	G
1	Analysis of Duplicates (put your own title in A1)
2
3	Name of Test (put your own in A3)
4	Preliminary Tests			Daily Tests
5	Test	#1	#2	Test	#1	#2	Difference
6	1			1
7	2			2
8	3			3
9	4			4
10	5			5
11	6			6
12	7			7
13	8	1.07	0.94	8
14	9		0.89	9
15	10	2.34	628.00	10
16	11	0.92	98.00	11
17	12	2.71	681.00	12
18	13	2.57	#VALUE!	13
19	14	2.95	Test	14
20	15	2.69	1.92	15
21	16	1.41	1.68	16
22	17	1.92	1.91	17
23	18	1.16	1.04	18
24	19	4.93	4.91	19
25	20	1.55	1.52	20
26	STATISTICS			21
27	for Preliminary			22
28	tests			23
29	Std Deviation			24
30	of the		#VALUE!	25
31	difference			26
32	UAL		#VALUE!	27
33	UWL		#VALUE!	28
34	LWL		#VALUE!	29
35	LAL		#VALUE!	30
36
37
38	date of test 1 prelim test (sample date)
39	#VALUE!
40	Date of first test (sample date)
41	3/22/2023
Duplicate-solution_Template

Cell Formulas
Range	Formula
C13,C20:C24	C13	=INDEX(BOD!N:V,MATCH($A$41,BOD!V:V,0)-(4+($A$25-A13)*11),4)
B13:B25	B13	=INDEX('bod and tss charting helper fil'!$D$5:$H$685,(COUNT('bod and tss charting helper fil'!D:D))-((COUNT('bod and tss charting helper fil'!D:D))-MATCH('Duplicate-solution_Template'!$A$41,'bod and tss charting helper fil'!$D$5:$D$685,0))-$A$25+A12,3)
C15	C15	=COUNTIF('bod and tss charting helper fil'!E5:E685,"")
C16	C16	=COUNT('bod and tss charting helper fil'!D5:D685)
C17	C17	=COUNT('bod and tss charting helper fil'!C5:C685)
C18	C18	=COUNTIF('bod and tss charting helper fil'!E5:INDIRECT(ADDRESS(MATCH('Duplicate-solution_Template'!A41,'bod and tss charting helper fil'!E5:E685,0),5)),">0")
C19	C19	=INDIRECT("a" & 3+2)
C25	C25	=INDEX(BOD!N:V,MATCH(A41,BOD!V:V,0)-(($A$25-A24)*4),4)
C30	C30	=STDEV(V6:V25)
C32	C32	=3*C30
C33	C33	=2*C30
C34	C34	=-2*C30
C35	C35	=-3*C30
G6:G35	G6	=IF(AND(E6>0,F6>0),E6-F6,"")
A39	A39	=INDEX(BOD!T:V,MATCH($A$42,BOD!V:V)-(($A$27)*11)-8,3)
Press CTRL+SHIFT+ENTER to enter array formulas.