Calculate on every third Friday of the month and show results between positive & negative weeks

[15minoffame]

Hi everyone,
First, I'd like to wish Mr.Excel.com a belated 25th birthday! I know I can use the Mod function to calculate between every X number of rows, but how is it calculated when between every X week the number of weeks is not uniform? This spreadsheet shows the weekly return of Microsoft since 2021. Every third week of every month is the monthly expiration cycle. The problem is Yahoo starts each week on a Monday, but the expiration is every third Friday of the month. I highlighted a few in red so you get the idea.

I would like to know TWO averages.

1) The average of all the bold columns for only the red highlighted rows aka monthly expiration week.
2) In addition, is it even possible to calculate the avgs between positive & negative weeks? The reason for this is because the O-H (Open-High) is much higher when it's a positive return week while the O-L (Open-Low) is the higher number during the negative weeks.

Thank you!

Date	Open	High	Low	Close	G/L %	Pts	O-H	O-H %	O-L	O-L %
12/28/2020	224.45	227.18	219.68	216.6029			2.73		-4.77
1/4/2021	222.53	223	211.94	213.8761	-1.26%	-2.73	0.47	0.21%	-10.59	-4.76%
1/11/2021	218.47	218.91	212.03	207.0884	-3.17%	-6.79	0.44	0.20%	-6.44	-2.95%
1/18/2021	213.75	230.07	212.63	220.0406	6.25%	12.95	16.32	7.64%	-1.12	-0.52%
1/25/2021	229.12	242.64	224.22	225.8934	2.66%	5.85	13.52	5.90%	-4.90	-2.14%
2/1/2021	235.06	245.09	232.43	235.8656	4.41%	9.97	10.03	4.27%	-2.63	-1.12%
2/8/2021	243.15	245.92	240.81	238.5826	1.15%	2.72	2.77	1.14%	-2.34	-0.96%
2/15/2021	245.03	246.13	240.18	234.6677	-1.64%	-3.91	1.10	0.45%	-4.85	-1.98%
2/22/2021	237.42	237.93	227.88	226.8236	-3.34%	-7.84	0.51	0.21%	-9.54	-4.02%
3/1/2021	235.9	237.47	224.26	226.0623	-0.34%	-0.76	1.57	0.67%	-11.64	-4.93%
3/8/2021	231.37	239.17	227.13	230.1131	1.79%	4.05	7.80	3.37%	-4.24	-1.83%
3/15/2021	234.96	240.06	229.35	224.8422	-2.29%	-5.27	5.10	2.17%	-5.61	-2.39%
3/22/2021	230.27	241.05	230.14	230.8256	2.66%	5.98	10.78	4.68%	-0.13	-0.06%
3/29/2021	236.59	242.84	231.1	236.5553	2.48%	5.73	6.25	2.64%	-5.49	-2.32%
4/5/2021	242.76	255.99	242.7	249.7325	5.57%	13.18	13.23	5.45%	-0.06	-0.02%
4/12/2021	254.71	261	254.62	254.5055	1.91%	4.77	6.29	2.47%	-0.09	-0.04%
4/19/2021	260.19	261.78	255.64	254.9057	0.16%	0.40	1.59	0.61%	-4.55	-1.75%
4/26/2021	261.66	263.19	249	246.1502	-3.43%	-8.76	1.53	0.58%	-12.66	-4.84%
5/3/2021	253.4	254.35	244.69	246.4235	0.11%	0.27	0.95	0.37%	-8.71	-3.44%
5/10/2021	250.87	251.73	238.07	242.2166	-1.71%	-4.21	0.86	0.34%	-12.80	-5.10%
5/17/2021	246.55	248.33	238.6	239.3078	-1.20%	-2.91	1.78	0.72%	-7.95	-3.22%
5/24/2021	247.79	252.94	247.51	244.2727	2.07%	4.96	5.15	2.08%	-0.28	-0.11%
5/31/2021	251.23	251.65	243	245.3587	0.44%	1.09	0.42	0.17%	-8.23	-3.28%
6/7/2021	249.98	258.49	249.81	252.3049	2.83%	6.95	8.51	3.40%	-0.17	-0.07%
6/14/2021	257.9	262.3	254.42	253.8116	0.60%	1.51	4.40	1.71%	-3.48	-1.35%
6/21/2021	259.82	267.85	257.92	259.2805	2.15%	5.47	8.03	3.09%	-1.90	-0.73%
6/28/2021	266.19	278	265.91	271.637	4.77%	12.36	11.81	4.44%	-0.28	-0.11%
7/5/2021	278.03	280.69	274.3	271.9207	0.10%	0.28	2.66	0.96%	-3.73	-1.34%
7/12/2021	279.16	284.1	276.58	274.6699	1.01%	2.75	4.94	1.77%	-2.58	-0.92%
7/19/2021	278.93	289.99	274.45	283.3967	3.18%	8.73	11.06	3.97%	-4.48	-1.61%
7/26/2021	289	290.15	282.95	278.7398	-1.64%	-4.66	1.15	0.40%	-6.05	-2.09%
8/2/2021	286.36	289.63	283.74	283.1912	1.60%	4.45	3.27	1.14%	-2.62	-0.91%
8/9/2021	289.75	292.9	285.2	286.5078	1.17%	3.32	3.15	1.09%	-4.55	-1.57%
8/16/2021	293.19	305.84	288.64	297.7685	3.93%	11.26	12.65	4.31%	-4.55	-1.55%
8/23/2021	303.25	305.65	296.83	293.7904	-1.34%	-3.98	2.40	0.79%	-6.42	-2.12%
8/30/2021	301.12	305.19	300.18	295.1823	0.47%	1.39	4.07	1.35%	-0.94	-0.31%
9/6/2021	301.01	302.14	295.38	289.8597	-1.80%	-5.32	1.13	0.38%	-5.63	-1.87%
9/13/2021	297.55	305.32	294.08	293.9374	1.41%	4.08	7.77	2.61%	-3.47	-1.17%
9/20/2021	296.33	300.9	289.52	293.4277	-0.17%	-0.51	4.57	1.54%	-6.81	-2.30%
9/27/2021	296.14	296.47	281.29	283.3805	-3.42%	-10.05	0.33	0.11%	-14.85	-5.01%
10/4/2021	287.4	296.64	280.25	289.0168	1.99%	5.64	9.24	3.22%	-7.15	-2.49%
10/11/2021	292.92	304.45	292.35	298.1915	3.17%	9.17	11.53	3.94%	-0.57	-0.19%
10/18/2021	303.57	311.09	302.69	303.0436	1.63%	4.85	7.52	2.48%	-0.88	-0.29%
10/25/2021	309.36	332	306.46	325.0593	7.26%	22.02	22.64	7.32%	-2.90	-0.94%
11/1/2021	331.36	338.79	326.37	329.4115	1.34%	4.35	7.43	2.24%	-4.99	-1.51%
11/8/2021	337.3	338.72	329.92	330.0584	0.20%	0.65	1.42	0.42%	-7.38	-2.19%
11/15/2021	337.54	345.1	334.03	336.3219	1.90%	6.26	7.56	2.24%	-3.51	-1.04%
11/22/2021	344.62	349.67	328.12	323.7488	-3.74%	-12.57	5.05	1.47%	-16.50	-4.79%
11/29/2021	334.94	339.28	318.03	317.1989	-2.02%	-6.55	4.34	1.30%	-16.91	-5.05%
12/6/2021	323.95	343	319.23	336.3775	6.05%	19.18	19.05	5.88%	-4.72	-1.46%
12/13/2021	340.68	343.79	317.25	317.9747	-5.47%	-18.40	3.11	0.91%	-23.43	-6.88%
12/20/2021	320.05	336.39	317.57	328.6688	3.36%	10.69	16.34	5.11%	-2.48	-0.77%
12/27/2021	335.46	344.3	335.43	330.2694	0.49%	1.60	8.84	2.64%	-0.03	-0.01%
1/3/2022	335.35	338	310.09	308.3903	-6.62%	-21.88	2.65	0.79%	-25.26	-7.53%
1/10/2022	309.49	323.41	303.75	304.6193	-1.22%	-3.77	13.92	4.50%	-5.74	-1.85%
1/17/2022	304.07	313.91	295.61	290.7043	-4.57%	-13.92	9.84	3.24%	-8.46	-2.78%
1/24/2022	292.2	308.5	276.05	302.7143	4.13%	12.01	16.30	5.58%	-16.15	-5.53%
1/31/2022	308.95	315.12	299.96	300.436	-0.75%	-2.28	6.17	2.00%	-8.99	-2.91%
2/7/2022	306.17	311.93	294.22	289.7321	-3.56%	-10.70	5.76	1.88%	-11.95	-3.90%
2/14/2022	293.77	300.87	286.31	282.7499	-2.41%	-6.98	7.10	2.42%	-7.46	-2.54%
2/21/2022	285	297.63	271.52	292.5649	3.47%	9.81	12.63	4.43%	-13.48	-4.73%
2/28/2022	294.31	303.13	287.17	285.2338	-2.51%	-7.33	8.82	3.00%	-7.14	-2.43%
3/7/2022	288.53	289.69	270	275.6001	-3.38%	-9.63	1.16	0.40%	-18.53	-6.42%
3/14/2022	280.34	301	275.82	295.6351	7.27%	20.04	20.66	7.37%	-4.52	-1.61%
3/21/2022	298.89	305.5	294.9	298.8333	1.08%	3.20	6.61	2.21%	-3.99	-1.33%
3/28/2022	304.33	315.95	304.33	304.4817	1.89%	5.65	11.62	3.82%	0.00	0.00%
4/4/2022	310.09	315.11	296.28	292.2304	-4.02%	-12.25	5.02	1.62%	-13.81	-4.45%
4/11/2022	291.79	292.61	279.32	275.3639	-5.77%	-16.87	0.82	0.28%	-12.47	-4.27%
4/18/2022	278.91	293.3	273.38	269.6565	-2.07%	-5.71	14.39	5.16%	-5.53	-1.98%
4/25/2022	273.29	290.98	270	273.0908	1.27%	3.43	17.69	6.47%	-3.29	-1.20%
5/2/2022	277.71	290.88	271.27	270.3453	-1.01%	-2.75	13.17	4.74%	-6.44	-2.32%
5/9/2022	270.06	273.75	250.02	256.9525	-4.95%	-13.39	3.69	1.37%	-20.04	-7.42%
5/16/2022	259.96	268.33	246.44	248.5291	-3.28%	-8.42	8.37	3.22%	-13.52	-5.20%
5/23/2022	255.49	273.34	253.43	269.5053	8.44%	20.98	17.85	6.99%	-2.06	-0.81%
5/30/2022	272.53	277.69	261.6	266.3293	-1.18%	-3.18	5.16	1.89%	-10.93	-4.01%
6/6/2022	272.06	274.18	252.53	249.5321	-6.31%	-16.80	2.12	0.78%	-19.53	-7.18%
6/13/2022	245.11	255.3	241.51	244.2651	-2.11%	-5.27	10.19	4.16%	-3.60	-1.47%
6/20/2022	250.26	267.98	249.51	264.041	8.10%	19.78	17.72	7.08%	-0.75	-0.30%
6/27/2022	268.21	268.3	252.9	256.032	-3.03%	-8.01	0.09	0.03%	-15.31	-5.71%
7/4/2022	256.16	269.06	254.74	264.0016	3.11%	7.97	12.90	5.04%	-1.42	-0.55%
7/11/2022	265.65	266.53	245.94	253.2111	-4.09%	-10.79	0.88	0.33%	-19.71	-7.42%
7/18/2022	259.75	265.33	253.3	256.8014	1.42%	3.59	5.58	2.15%	-6.45	-2.48%
7/25/2022	261	282	249.57	276.9027	7.83%	20.10	21.00	8.05%	-11.43	-4.38%
8/1/2022	277.82	283.8	272.38	279.0432	0.77%	2.14	5.98	2.15%	-5.44	-1.96%
8/8/2022	284.05	291.91	277.61	287.9201	3.18%	8.88	7.86	2.77%	-6.44	-2.27%
8/15/2022	291	294.18	285.56	282.2388	-1.97%	-5.68	3.18	1.09%	-5.44	-1.87%
8/22/2022	282.08	282.46	267.98	264.987	-6.11%	-17.25	0.38	0.13%	-14.10	-5.00%
8/29/2022	265.85	267.4	254.47	253.0962	-4.49%	-11.89	1.55	0.58%	-11.38	-4.28%
9/5/2022	256.2	265.23	251.94	261.399	3.28%	8.30	9.03	3.52%	-4.26	-1.66%
9/12/2022	265.78	267.45	242.06	241.9073	-7.46%	-19.49	1.67	0.63%	-23.72	-8.92%
9/19/2022	242.47	247.66	235.2	235.1662	-2.79%	-6.74	5.19	2.14%	-7.27	-3.00%
9/26/2022	237.05	242.33	232.73	230.2043	-2.11%	-4.96	5.28	2.23%	-4.32	-1.82%
10/3/2022	235.41	250.58	233.17	231.5288	0.58%	1.32	15.17	6.44%	-2.24	-0.95%
10/10/2022	233.05	237.24	219.13	225.9145	-2.42%	-5.61	4.19	1.80%	-13.92	-5.97%
10/17/2022	235.82	243.93	234.29	239.3176	5.93%	13.40	8.11	3.44%	-1.53	-0.65%
10/24/2022	243.76	251.04	225.78	233.1399	-2.58%	-6.18	7.28	2.99%	-17.98	-7.38%
10/31/2022	233.76	235.74	213.43	218.8275	-6.14%	-14.31	1.98	0.85%	-20.33	-8.70%
11/7/2022	221.99	247.99	221.28	244.2498	11.62%	25.42	26.00	11.71%	-0.71	-0.32%
11/14/2022	241.99	247	237.63	238.428	-2.38%	-5.82	5.01	2.07%	-4.36	-1.80%
11/21/2022	241.43	248.7	240.71	245.3148	2.89%	6.89	7.27	3.01%	-0.72	-0.30%
11/28/2022	246.08	256.12	238.21	252.7786	3.04%	7.46	10.04	4.08%	-7.87	-3.20%
12/5/2022	252.01	253.82	242.21	243.263	-3.76%	-9.52	1.81	0.72%	-9.80	-3.89%
12/12/2022	247.45	263.92	243.51	242.5394	-0.30%	-0.72	16.47	6.66%	-3.94	-1.59%
12/19/2022	244.86	245.62	233.87	236.6318	-2.44%	-5.91	0.76	0.31%	-10.99	-4.49%
12/26/2022	238.7	241.92	234.17	237.7122	0.46%	1.08	3.22	1.35%	-4.53	-1.90%
1/2/2023	243.08	245.75	219.35	222.9531	-6.21%	-14.76	2.67	1.10%	-23.73	-9.76%
1/9/2023	226.45	239.9	226.41	237.1274	6.36%	14.17	13.45	5.94%	-0.04	-0.02%
1/16/2023	237.97	242.38	230.68	238.1087	0.41%	0.98	4.41	1.85%	-7.29	-3.06%
1/23/2023	241.1	249.83	230.9	245.9789	3.31%	7.87	8.73	3.62%	-10.20	-4.23%
1/30/2023	244.51	264.69	242.2	256.0794	4.11%	10.10	20.18	8.25%	-2.31	-0.94%
2/6/2023	257.44	276.76	254.78	260.7876	1.84%	4.71	19.32	7.50%	-2.66	-1.03%
2/13/2023	267.64	274.97	256	255.7919	-1.92%	-5.00	7.33	2.74%	-11.64	-4.35%
2/20/2023	254.48	256.84	248.1	247.6483	-3.18%	-8.14	2.36	0.93%	-6.38	-2.51%
2/27/2023	252.46	255.62	245.61	253.6801	2.44%	6.03	3.16	1.25%	-6.85	-2.71%
3/6/2023	256.43	260.12	247.6	247.0223	-2.62%	-6.66	3.69	1.44%	-8.83	-3.44%
3/13/2023	247.4	283.33	245.73	277.6678	12.41%	30.65	35.93	14.52%	-1.67	-0.68%
3/20/2023	276.98	281.06	269.52	278.8007	0.41%	1.13	4.08	1.47%	-7.46	-2.69%
3/27/2023	280.5	289.27	272.05	286.4819	2.76%	7.68	8.77	3.13%	-8.45	-3.01%
4/3/2023	286.52	292.08	282.03	289.7611	1.14%	3.28	5.56	1.94%	-4.49	-1.57%
4/10/2023	289.21	289.9	281.64	284.3355	-1.87%	-5.43	0.69	0.24%	-7.57	-2.62%
4/17/2023	289.93	291.76	283.06	283.9579	-0.13%	-0.38	1.83	0.63%	-6.87	-2.37%
4/24/2023	282.09	308.93	275.37	305.3224	7.52%	21.36	26.84	9.51%	-6.72	-2.38%
5/1/2023	306.97	311.97	303.4	308.6909	1.10%	3.37	5.00	1.63%	-3.57	-1.16%
5/8/2023	310.13	313	306.09	307.0216	-0.54%	-1.67	2.87	0.93%	-4.04	-1.30%
5/15/2023	309.1	319.04	307.59	316.3325	3.03%	9.31	9.94	3.22%	-1.51	-0.49%
5/22/2023	318.6	333.4	312.61	331.5139	4.80%	15.18	14.80	4.65%	-5.99	-1.88%
5/29/2023	335.23	337.5	324.72	334.0134	0.75%	2.50	2.27	0.68%	-10.51	-3.14%
6/5/2023	335.22	338.56	322.5	325.4391	-2.57%	-8.57	3.34	1.00%	-12.72	-3.79%
6/12/2023	328.58	351.47	325.16	340.9148	4.76%	15.48	22.89	6.97%	-3.42	-1.04%
6/19/2023	339.31	342.08	332.07	333.635	-2.14%	-7.28	2.77	0.82%	-7.24	-2.13%
6/26/2023	333.72	342.73	328.49	339.1322	1.65%	5.50	9.01	2.70%	-5.23	-1.57%
7/3/2023	339.19	342.99	334.73	335.8259	-0.97%	-3.31	3.80	1.12%	-4.46	-1.31%
7/10/2023	334.6	351.43	327	343.8127	2.38%	7.99	16.83	5.03%	-7.60	-2.27%
7/17/2023	345.68	366.78	339.83	342.3488	-0.43%	-1.46	21.10	6.10%	-5.85	-1.69%
7/24/2023	345.85	351.89	329.05	336.9712	-1.57%	-5.38	6.04	1.75%	-16.80	-4.86%
7/31/2023	336.92	338.54	325.95	326.425	-3.13%	-10.55	1.62	0.48%	-10.97	-3.26%
8/7/2023	328.37	331.11	319.21	319.683	-2.07%	-6.74	2.74	0.83%	-9.16	-2.79%
8/14/2023	321.39	325.09	311.55	315.1717	-1.41%	-4.51	3.70	1.15%	-9.84	-3.06%
8/21/2023	317.93	332.98	317.04	322.3258	2.27%	7.15	15.05	4.73%	-0.89	-0.28%
8/28/2023	325.66	331.99	321.72	327.9943	1.76%	5.67	6.33	1.94%	-3.94	-1.21%
9/4/2023	329	336.16	328.66	333.5929	1.71%	5.60	7.16	2.18%	-0.34	-0.10%
9/11/2023	337.24	340.86	329.65	329.5511	-1.21%	-4.04	3.62	1.07%	-7.59	-2.25%
9/18/2023	327.8	330.4	315	316.3679	-4.00%	-13.18	2.60	0.79%	-12.80	-3.90%
9/25/2023	316.59	319.47	309.45	315.1104	-0.40%	-1.26	2.88	0.91%	-7.14	-2.26%
10/2/2023	316.28	329.19	311.21	326.5971	3.65%	11.49	12.91	4.08%	-5.07	-1.60%
10/9/2023	324.75	333.83	323.18	327.0662	0.14%	0.47	9.08	2.80%	-1.57	-0.48%
10/16/2023	331.05	336.88	325.45	326.0083	-0.32%	-1.06	5.83	1.76%	-5.60	-1.69%
10/23/2023	325.47	346.2	324.39	329.142	0.96%	3.13	20.73	6.37%	-1.08	-0.33%
10/30/2023	333.41	354.39	331.83	352.0854	6.97%	22.94	20.98	6.29%	-1.58	-0.47%
11/6/2023	353.45	370.1	353.35	368.9212	4.78%	16.84	16.65	4.71%	-0.10	-0.03%
11/13/2023	368.22	376.35	365.9	369.1009	0.05%	0.18	8.13	2.21%	-2.32	-0.63%
11/20/2023	371.22	379.79	371	377.43	2.26%	8.33	8.57	2.31%	-0.22	-0.06%
11/27/2023	376.78	384.3	371.31	374.51	-0.77%	-2.92	7.52	2.00%	-5.47	-1.45%
12/4/2023	369.1	374.46	362.9	374.23	-0.07%	-0.28	5.36	1.45%	-6.20	-1.68%
12/11/2023	368.48	377.64	364.13	370.73	-0.94%	-3.50	9.16	2.49%	-4.35	-1.18%
12/18/2023	369.45	376.03	368.68	374.58	1.04%	3.85	6.58	1.78%	-0.77	-0.21%
12/25/2023	375	377.16	372.81	376.04	0.39%	1.46	2.16	0.58%	-2.19	-0.58%

 

[awoohaw]

what version of excel are you using? There are functions available in later versions that may make your solution different from what others use?

Your dates start on Mondays, but you ask for 3rd fridays. What if the first friday is less than the 4th of the month .... what day is the 3rd Friday?

 

[awoohaw]

The xl2bb add in won't allow a large paste. So, I've truncated it to 98 rows. Below the formula sections is a pivot table of averages.
I needed the helper column for the pivot, but it isn't necessary for the array formulas. It may not be needed for the pivot if you can somehow calculate it.

This only answers your first question. I'm not sure what you're asking for in the second.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	3rd Friday	Open	High	Low	Close	G/L %	Pts	O-H	O-H %	O-L	O-L %
2	Mon 2020-12-28	FALSE	224.45	227.18	219.68	216.6029			2.73		-4.77
3	Mon 2021-01-04	FALSE	222.53	223.00	211.94	213.8761	-1%	-2.73	0.47	0%	-10.59	-5%
4	Mon 2021-01-11	TRUE	218.47	218.91	212.03	207.0884	-3%	-6.79	0.44	0%	-6.44	-3%
5	Mon 2021-01-18	FALSE	213.75	230.07	212.63	220.0406	6%	12.95	16.32	8%	-1.12	-1%
6	Mon 2021-01-25	FALSE	229.12	242.64	224.22	225.8934	3%	5.85	13.52	6%	-4.90	-2%
7	Mon 2021-02-01	FALSE	235.06	245.09	232.43	235.8656	4%	9.97	10.03	4%	-2.63	-1%
8	Mon 2021-02-08	FALSE	243.15	245.92	240.81	238.5826	1%	2.72	2.77	1%	-2.34	-1%
9	Mon 2021-02-15	TRUE	245.03	246.13	240.18	234.6677	-2%	-3.91	1.10	0%	-4.85	-2%
10	Mon 2021-02-22	FALSE	237.42	237.93	227.88	226.8236	-3%	-7.84	0.51	0%	-9.54	-4%
11	Mon 2021-03-01	FALSE	235.90	237.47	224.26	226.0623	0%	-0.76	1.57	1%	-11.64	-5%
12	Mon 2021-03-08	FALSE	231.37	239.17	227.13	230.1131	2%	4.05	7.80	3%	-4.24	-2%
13	Mon 2021-03-15	TRUE	234.96	240.06	229.35	224.8422	-2%	-5.27	5.10	2%	-5.61	-2%
14	Mon 2021-03-22	FALSE	230.27	241.05	230.14	230.8256	3%	5.98	10.78	5%	-0.13	0%
15	Mon 2021-03-29	FALSE	236.59	242.84	231.10	236.5553	2%	5.73	6.25	3%	-5.49	-2%
16	Mon 2021-04-05	FALSE	242.76	255.99	242.70	249.7325	6%	13.18	13.23	5%	-0.06	0%
17	Mon 2021-04-12	TRUE	254.71	261.00	254.62	254.5055	2%	4.77	6.29	2%	-0.09	0%
18	Mon 2021-04-19	FALSE	260.19	261.78	255.64	254.9057	0%	0.40	1.59	1%	-4.55	-2%
19	Mon 2021-04-26	FALSE	261.66	263.19	249.00	246.1502	-3%	-8.76	1.53	1%	-12.66	-5%
20	Mon 2021-05-03	FALSE	253.40	254.35	244.69	246.4235	0%	0.27	0.95	0%	-8.71	-3%
21	Mon 2021-05-10	FALSE	250.87	251.73	238.07	242.2166	-2%	-4.21	0.86	0%	-12.80	-5%
22	Mon 2021-05-17	TRUE	246.55	248.33	238.60	239.3078	-1%	-2.91	1.78	1%	-7.95	-3%
23	Mon 2021-05-24	FALSE	247.79	252.94	247.51	244.2727	2%	4.96	5.15	2%	-0.28	0%
24	Mon 2021-05-31	FALSE	251.23	251.65	243.00	245.3587	0%	1.09	0.42	0%	-8.23	-3%
25	Mon 2021-06-07	FALSE	249.98	258.49	249.81	252.3049	3%	6.95	8.51	3%	-0.17	0%
26	Mon 2021-06-14	TRUE	257.90	262.30	254.42	253.8116	1%	1.51	4.40	2%	-3.48	-1%
27	Mon 2021-06-21	FALSE	259.82	267.85	257.92	259.2805	2%	5.47	8.03	3%	-1.90	-1%
28	Mon 2021-06-28	FALSE	266.19	278.00	265.91	271.6370	5%	12.36	11.81	4%	-0.28	0%
29	Mon 2021-07-05	FALSE	278.03	280.69	274.30	271.9207	0%	0.28	2.66	1%	-3.73	-1%
30	Mon 2021-07-12	TRUE	279.16	284.10	276.58	274.6699	1%	2.75	4.94	2%	-2.58	-1%
31	Mon 2021-07-19	FALSE	278.93	289.99	274.45	283.3967	3%	8.73	11.06	4%	-4.48	-2%
32	Mon 2021-07-26	FALSE	289.00	290.15	282.95	278.7398	-2%	-4.66	1.15	0%	-6.05	-2%
33	Mon 2021-08-02	FALSE	286.36	289.63	283.74	283.1912	2%	4.45	3.27	1%	-2.62	-1%
34	Mon 2021-08-09	FALSE	289.75	292.90	285.20	286.5078	1%	3.32	3.15	1%	-4.55	-2%
35	Mon 2021-08-16	TRUE	293.19	305.84	288.64	297.7685	4%	11.26	12.65	4%	-4.55	-2%
36	Mon 2021-08-23	FALSE	303.25	305.65	296.83	293.7904	-1%	-3.98	2.40	1%	-6.42	-2%
37	Mon 2021-08-30	FALSE	301.12	305.19	300.18	295.1823	0%	1.39	4.07	1%	-0.94	0%
38	Mon 2021-09-06	FALSE	301.01	302.14	295.38	289.8597	-2%	-5.32	1.13	0%	-5.63	-2%
39	Mon 2021-09-13	TRUE	297.55	305.32	294.08	293.9374	1%	4.08	7.77	3%	-3.47	-1%
40	Mon 2021-09-20	FALSE	296.33	300.90	289.52	293.4277	0%	-0.51	4.57	2%	-6.81	-2%
41	Mon 2021-09-27	FALSE	296.14	296.47	281.29	283.3805	-3%	-10.05	0.33	0%	-14.85	-5%
42	Mon 2021-10-04	FALSE	287.40	296.64	280.25	289.0168	2%	5.64	9.24	3%	-7.15	-2%
43	Mon 2021-10-11	TRUE	292.92	304.45	292.35	298.1915	3%	9.17	11.53	4%	-0.57	0%
44	Mon 2021-10-18	FALSE	303.57	311.09	302.69	303.0436	2%	4.85	7.52	2%	-0.88	0%
45	Mon 2021-10-25	FALSE	309.36	332.00	306.46	325.0593	7%	22.02	22.64	7%	-2.90	-1%
46	Mon 2021-11-01	FALSE	331.36	338.79	326.37	329.4115	1%	4.35	7.43	2%	-4.99	-2%
47	Mon 2021-11-08	FALSE	337.30	338.72	329.92	330.0584	0%	0.65	1.42	0%	-7.38	-2%
48	Mon 2021-11-15	TRUE	337.54	345.10	334.03	336.3219	2%	6.26	7.56	2%	-3.51	-1%
49	Mon 2021-11-22	FALSE	344.62	349.67	328.12	323.7488	-4%	-12.57	5.05	1%	-16.50	-5%
50	Mon 2021-11-29	FALSE	334.94	339.28	318.03	317.1989	-2%	-6.55	4.34	1%	-16.91	-5%
51	Mon 2021-12-06	FALSE	323.95	343.00	319.23	336.3775	6%	19.18	19.05	6%	-4.72	-1%
52	Mon 2021-12-13	TRUE	340.68	343.79	317.25	317.9747	-5%	-18.40	3.11	1%	-23.43	-7%
53	Mon 2021-12-20	FALSE	320.05	336.39	317.57	328.6688	3%	10.69	16.34	5%	-2.48	-1%
54	Mon 2021-12-27	FALSE	335.46	344.30	335.43	330.2694	0%	1.60	8.84	3%	-0.03	0%
55	Mon 2022-01-03	FALSE	335.35	338.00	310.09	308.3903	-7%	-21.88	2.65	1%	-25.26	-8%
56	Mon 2022-01-10	FALSE	309.49	323.41	303.75	304.6193	-1%	-3.77	13.92	5%	-5.74	-2%
57	Mon 2022-01-17	TRUE	304.07	313.91	295.61	290.7043	-5%	-13.92	9.84	3%	-8.46	-3%
58	Mon 2022-01-24	FALSE	292.20	308.50	276.05	302.7143	4%	12.01	16.30	6%	-16.15	-6%
59	Mon 2022-01-31	FALSE	308.95	315.12	299.96	300.4360	-1%	-2.28	6.17	2%	-8.99	-3%
60	Mon 2022-02-07	FALSE	306.17	311.93	294.22	289.7321	-4%	-10.70	5.76	2%	-11.95	-4%
61	Mon 2022-02-14	TRUE	293.77	300.87	286.31	282.7499	-2%	-6.98	7.10	2%	-7.46	-3%
62	Mon 2022-02-21	FALSE	285.00	297.63	271.52	292.5649	3%	9.81	12.63	4%	-13.48	-5%
63	Mon 2022-02-28	FALSE	294.31	303.13	287.17	285.2338	-3%	-7.33	8.82	3%	-7.14	-2%
64	Mon 2022-03-07	FALSE	288.53	289.69	270.00	275.6001	-3%	-9.63	1.16	0%	-18.53	-6%
65	Mon 2022-03-14	TRUE	280.34	301.00	275.82	295.6351	7%	20.04	20.66	7%	-4.52	-2%
66	Mon 2022-03-21	FALSE	298.89	305.50	294.90	298.8333	1%	3.20	6.61	2%	-3.99	-1%
67	Mon 2022-03-28	FALSE	304.33	315.95	304.33	304.4817	2%	5.65	11.62	4%	0.00	0%
68	Mon 2022-04-04	FALSE	310.09	315.11	296.28	292.2304	-4%	-12.25	5.02	2%	-13.81	-4%
69	Mon 2022-04-11	TRUE	291.79	292.61	279.32	275.3639	-6%	-16.87	0.82	0%	-12.47	-4%
70	Mon 2022-04-18	FALSE	278.91	293.30	273.38	269.6565	-2%	-5.71	14.39	5%	-5.53	-2%
71	Mon 2022-04-25	FALSE	273.29	290.98	270.00	273.0908	1%	3.43	17.69	6%	-3.29	-1%
72	Mon 2022-05-02	FALSE	277.71	290.88	271.27	270.3453	-1%	-2.75	13.17	5%	-6.44	-2%
73	Mon 2022-05-09	FALSE	270.06	273.75	250.02	256.9525	-5%	-13.39	3.69	1%	-20.04	-7%
74	Mon 2022-05-16	TRUE	259.96	268.33	246.44	248.5291	-3%	-8.42	8.37	3%	-13.52	-5%
75	Mon 2022-05-23	FALSE	255.49	273.34	253.43	269.5053	8%	20.98	17.85	7%	-2.06	-1%
76	Mon 2022-05-30	FALSE	272.53	277.69	261.60	266.3293	-1%	-3.18	5.16	2%	-10.93	-4%
77	Mon 2022-06-06	FALSE	272.06	274.18	252.53	249.5321	-6%	-16.80	2.12	1%	-19.53	-7%
78	Mon 2022-06-13	TRUE	245.11	255.30	241.51	244.2651	-2%	-5.27	10.19	4%	-3.60	-1%
79	Mon 2022-06-20	FALSE	250.26	267.98	249.51	264.0410	8%	19.78	17.72	7%	-0.75	0%
80	Mon 2022-06-27	FALSE	268.21	268.30	252.90	256.0320	-3%	-8.01	0.09	0%	-15.31	-6%
81	Mon 2022-07-04	FALSE	256.16	269.06	254.74	264.0016	3%	7.97	12.90	5%	-1.42	-1%
82	Mon 2022-07-11	TRUE	265.65	266.53	245.94	253.2111	-4%	-10.79	0.88	0%	-19.71	-7%
83	Mon 2022-07-18	FALSE	259.75	265.33	253.30	256.8014	1%	3.59	5.58	2%	-6.45	-2%
84	Mon 2022-07-25	FALSE	261.00	282.00	249.57	276.9027	8%	20.10	21.00	8%	-11.43	-4%
85	Mon 2022-08-01	FALSE	277.82	283.80	272.38	279.0432	1%	2.14	5.98	2%	-5.44	-2%
86	Mon 2022-08-08	FALSE	284.05	291.91	277.61	287.9201	3%	8.88	7.86	3%	-6.44	-2%
87	Mon 2022-08-15	TRUE	291.00	294.18	285.56	282.2388	-2%	-5.68	3.18	1%	-5.44	-2%
88	Mon 2022-08-22	FALSE	282.08	282.46	267.98	264.9870	-6%	-17.25	0.38	0%	-14.10	-5%
89	Mon 2022-08-29	FALSE	265.85	267.40	254.47	253.0962	-4%	-11.89	1.55	1%	-11.38	-4%
90	Mon 2022-09-05	FALSE	256.20	265.23	251.94	261.3990	3%	8.30	9.03	4%	-4.26	-2%
91	Mon 2022-09-12	TRUE	265.78	267.45	242.06	241.9073	-7%	-19.49	1.67	1%	-23.72	-9%
92	Mon 2022-09-19	FALSE	242.47	247.66	235.20	235.1662	-3%	-6.74	5.19	2%	-7.27	-3%
93	Mon 2022-09-26	FALSE	237.05	242.33	232.73	230.2043	-2%	-4.96	5.28	2%	-4.32	-2%
94	Mon 2022-10-03	FALSE	235.41	250.58	233.17	231.5288	1%	1.32	15.17	6%	-2.24	-1%
95	Mon 2022-10-10	FALSE	233.05	237.24	219.13	225.9145	-2%	-5.61	4.19	2%	-13.92	-6%
96	Mon 2022-10-17	TRUE	235.82	243.93	234.29	239.3176	6%	13.40	8.11	3%	-1.53	-1%
97	Mon 2022-10-24	FALSE	243.76	251.04	225.78	233.1399	-3%	-6.18	7.28	3%	-17.98	-7%
98	Mon 2022-10-31	FALSE	233.76	235.74	213.43	218.8275	-6%	-14.31	1.98	1%	-20.33	-9%
99	Mon 2022-11-07	FALSE	221.99	247.99	221.28	244.2498	12%	25.42	26.00	12%	-0.71	0%
100
101		274.18	280.43	266.59	267.59
102
103
104
105		3rd Friday	True
106
107		Average of Open	Average of High	Average of Low	Average of Close
108		274.1795455	280.4290909	266.5904545	267.5913318
Sheet4

Cell Formulas
Range	Formula
B2:B99	B2	=AND((DAY((A2)+4)<=21)*(DAY((A2)+4)>=15)*(WEEKDAY((A2)+4,2)=5))
B101:E101	B101	=SUM((DAY(($A2:$A99)+4)<=21)*(DAY(($A2:$A99)+4)>=15)*(WEEKDAY(($A2:$A99)+4,2)=5)*(C2:C99)) / SUM((DAY(($A2:$A99)+4)<=21)*(DAY(($A2:$A99)+4)>=15)*(WEEKDAY(($A2:$A99)+4,2)=5))

 

[15minoffame]

Hi,
I have Excel 2016. Do I need the B2 column showing the True & False? I plugged in the B101 formula under the 'G/L %' column but got a "value" error. I would like the avg of columns G-L for only the True rows.

 

[awoohaw]

Regarding column B (True False). I mentioned that I only created that to make the PIVOT Table at the bottom (xl2bb doesn't copy PIVOT table functionality only the cells so you can't tell it is a pivot unless I've stated that).

As far as using the average column formula for your percentage columns. You can not just copy that formula to get your averages unless you actually want the percentage of averages. But, if that is what you want try what is below. But, I suggest you calculate yourself manually to be sure your expectation matches a formula.
I'm not going to guess how to do the row by row calculations. Please post the calculation formulas for cells in columns F-K?

But, Dragging the formulas over seems to work for me. Check your cell references. If the formulas have been altered then debug on your end.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Open	High	Low	Close	G/L %	Pts	O-H	O-H %	O-L	O-L %
2	2020-12-28	224.45	227.18	219.68	216.6029			2.73		-4.77
3	2021-01-04	222.53	223	211.94	213.8761	-1.26%	-2.73	0.47	0.21%	-10.59	-4.76%
4	2021-01-11	218.47	218.91	212.03	207.0884	-3.17%	-6.79	0.44	0.20%	-6.44	-2.95%
5	2021-01-18	213.75	230.07	212.63	220.0406	6.25%	12.95	16.32	7.64%	-1.12	-0.52%
6	2021-01-25	229.12	242.64	224.22	225.8934	2.66%	5.85	13.52	5.90%	-4.9	-2.14%
7	2021-02-01	235.06	245.09	232.43	235.8656	4.41%	9.97	10.03	4.27%	-2.63	-1.12%
8	2021-02-08	243.15	245.92	240.81	238.5826	1.15%	2.72	2.77	1.14%	-2.34	-0.96%
9	2021-02-15	245.03	246.13	240.18	234.6677	-1.64%	-3.91	1.1	0.45%	-4.85	-1.98%
10	2021-02-22	237.42	237.93	227.88	226.8236	-3.34%	-7.84	0.51	0.21%	-9.54	-4.02%
11	2021-03-01	235.9	237.47	224.26	226.0623	-0.34%	-0.76	1.57	0.67%	-11.64	-4.93%
12	2021-03-08	231.37	239.17	227.13	230.1131	1.79%	4.05	7.8	3.37%	-4.24	-1.83%
13	2021-03-15	234.96	240.06	229.35	224.8422	-2.29%	-5.27	5.1	2.17%	-5.61	-2.39%
14	2021-03-22	230.27	241.05	230.14	230.8256	2.66%	5.98	10.78	4.68%	-0.13	-0.06%
15	2021-03-29	236.59	242.84	231.1	236.5553	2.48%	5.73	6.25	2.64%	-5.49	-2.32%
16	2021-04-05	242.76	255.99	242.7	249.7325	5.57%	13.18	13.23	5.45%	-0.06	-0.02%
17	2021-04-12	254.71	261	254.62	254.5055	1.91%	4.77	6.29	2.47%	-0.09	-0.04%
18	2021-04-19	260.19	261.78	255.64	254.9057	0.16%	0.4	1.59	0.61%	-4.55	-1.75%
19	2021-04-26	261.66	263.19	249	246.1502	-3.43%	-8.76	1.53	0.58%	-12.66	-4.84%
20	2021-05-03	253.4	254.35	244.69	246.4235	0.11%	0.27	0.95	0.37%	-8.71	-3.44%
21	2021-05-10	250.87	251.73	238.07	242.2166	-1.71%	-4.21	0.86	0.34%	-12.8	-5.10%
22	2021-05-17	246.55	248.33	238.6	239.3078	-1.20%	-2.91	1.78	0.72%	-7.95	-3.22%
23	2021-05-24	247.79	252.94	247.51	244.2727	2.07%	4.96	5.15	2.08%	-0.28	-0.11%
24	2021-05-31	251.23	251.65	243	245.3587	0.44%	1.09	0.42	0.17%	-8.23	-3.28%
25	2021-06-07	249.98	258.49	249.81	252.3049	2.83%	6.95	8.51	3.40%	-0.17	-0.07%
26	2021-06-14	257.9	262.3	254.42	253.8116	0.60%	1.51	4.4	1.71%	-3.48	-1.35%
27	2021-06-21	259.82	267.85	257.92	259.2805	2.15%	5.47	8.03	3.09%	-1.9	-0.73%
28	2021-06-28	266.19	278	265.91	271.637	4.77%	12.36	11.81	4.44%	-0.28	-0.11%
29	2021-07-05	278.03	280.69	274.3	271.9207	0.10%	0.28	2.66	0.96%	-3.73	-1.34%
30	2021-07-12	279.16	284.1	276.58	274.6699	1.01%	2.75	4.94	1.77%	-2.58	-0.92%
31	2021-07-19	278.93	289.99	274.45	283.3967	3.18%	8.73	11.06	3.97%	-4.48	-1.61%
32	2021-07-26	289	290.15	282.95	278.7398	-1.64%	-4.66	1.15	0.40%	-6.05	-2.09%
33	2021-08-02	286.36	289.63	283.74	283.1912	1.60%	4.45	3.27	1.14%	-2.62	-0.91%
34	2021-08-09	289.75	292.9	285.2	286.5078	1.17%	3.32	3.15	1.09%	-4.55	-1.57%
35	2021-08-16	293.19	305.84	288.64	297.7685	3.93%	11.26	12.65	4.31%	-4.55	-1.55%
36	2021-08-23	303.25	305.65	296.83	293.7904	-1.34%	-3.98	2.4	0.79%	-6.42	-2.12%
37	2021-08-30	301.12	305.19	300.18	295.1823	0.47%	1.39	4.07	1.35%	-0.94	-0.31%
38	2021-09-06	301.01	302.14	295.38	289.8597	-1.80%	-5.32	1.13	0.38%	-5.63	-1.87%
39	2021-09-13	297.55	305.32	294.08	293.9374	1.41%	4.08	7.77	2.61%	-3.47	-1.17%
40	2021-09-20	296.33	300.9	289.52	293.4277	-0.17%	-0.51	4.57	1.54%	-6.81	-2.30%
41	2021-09-27	296.14	296.47	281.29	283.3805	-3.42%	-10.05	0.33	0.11%	-14.85	-5.01%
42	2021-10-04	287.4	296.64	280.25	289.0168	1.99%	5.64	9.24	3.22%	-7.15	-2.49%
43	2021-10-11	292.92	304.45	292.35	298.1915	3.17%	9.17	11.53	3.94%	-0.57	-0.19%
44	2021-10-18	303.57	311.09	302.69	303.0436	1.63%	4.85	7.52	2.48%	-0.88	-0.29%
45	2021-10-25	309.36	332	306.46	325.0593	7.26%	22.02	22.64	7.32%	-2.9	-0.94%
46	2021-11-01	331.36	338.79	326.37	329.4115	1.34%	4.35	7.43	2.24%	-4.99	-1.51%
47	2021-11-08	337.3	338.72	329.92	330.0584	0.20%	0.65	1.42	0.42%	-7.38	-2.19%
48	2021-11-15	337.54	345.1	334.03	336.3219	1.90%	6.26	7.56	2.24%	-3.51	-1.04%
49	2021-11-22	344.62	349.67	328.12	323.7488	-3.74%	-12.57	5.05	1.47%	-16.5	-4.79%
50	2021-11-29	334.94	339.28	318.03	317.1989	-2.02%	-6.55	4.34	1.30%	-16.91	-5.05%
51	2021-12-06	323.95	343	319.23	336.3775	6.05%	19.18	19.05	5.88%	-4.72	-1.46%
52	2021-12-13	340.68	343.79	317.25	317.9747	-5.47%	-18.4	3.11	0.91%	-23.43	-6.88%
53	2021-12-20	320.05	336.39	317.57	328.6688	3.36%	10.69	16.34	5.11%	-2.48	-0.77%
54	2021-12-27	335.46	344.3	335.43	330.2694	0.49%	1.6	8.84	2.64%	-0.03	-0.01%
55	2022-01-03	335.35	338	310.09	308.3903	-6.62%	-21.88	2.65	0.79%	-25.26	-7.53%
56	2022-01-10	309.49	323.41	303.75	304.6193	-1.22%	-3.77	13.92	4.50%	-5.74	-1.85%
57	2022-01-17	304.07	313.91	295.61	290.7043	-4.57%	-13.92	9.84	3.24%	-8.46	-2.78%
58	2022-01-24	292.2	308.5	276.05	302.7143	4.13%	12.01	16.3	5.58%	-16.15	-5.53%
59	2022-01-31	308.95	315.12	299.96	300.436	-0.75%	-2.28	6.17	2.00%	-8.99	-2.91%
60	2022-02-07	306.17	311.93	294.22	289.7321	-3.56%	-10.7	5.76	1.88%	-11.95	-3.90%
61	2022-02-14	293.77	300.87	286.31	282.7499	-2.41%	-6.98	7.1	2.42%	-7.46	-2.54%
62	2022-02-21	285	297.63	271.52	292.5649	3.47%	9.81	12.63	4.43%	-13.48	-4.73%
63	2022-02-28	294.31	303.13	287.17	285.2338	-2.51%	-7.33	8.82	3.00%	-7.14	-2.43%
64	2022-03-07	288.53	289.69	270	275.6001	-3.38%	-9.63	1.16	0.40%	-18.53	-6.42%
65	2022-03-14	280.34	301	275.82	295.6351	7.27%	20.04	20.66	7.37%	-4.52	-1.61%
66	2022-03-21	298.89	305.5	294.9	298.8333	1.08%	3.2	6.61	2.21%	-3.99	-1.33%
67	2022-03-28	304.33	315.95	304.33	304.4817	1.89%	5.65	11.62	3.82%	0	0.00%
68	2022-04-04	310.09	315.11	296.28	292.2304	-4.02%	-12.25	5.02	1.62%	-13.81	-4.45%
69	2022-04-11	291.79	292.61	279.32	275.3639	-5.77%	-16.87	0.82	0.28%	-12.47	-4.27%
70	2022-04-18	278.91	293.3	273.38	269.6565	-2.07%	-5.71	14.39	5.16%	-5.53	-1.98%
71	2022-04-25	273.29	290.98	270	273.0908	1.27%	3.43	17.69	6.47%	-3.29	-1.20%
72	2022-05-02	277.71	290.88	271.27	270.3453	-1.01%	-2.75	13.17	4.74%	-6.44	-2.32%
73	2022-05-09	270.06	273.75	250.02	256.9525	-4.95%	-13.39	3.69	1.37%	-20.04	-7.42%
74	2022-05-16	259.96	268.33	246.44	248.5291	-3.28%	-8.42	8.37	3.22%	-13.52	-5.20%
75	2022-05-23	255.49	273.34	253.43	269.5053	8.44%	20.98	17.85	6.99%	-2.06	-0.81%
76	2022-05-30	272.53	277.69	261.6	266.3293	-1.18%	-3.18	5.16	1.89%	-10.93	-4.01%
77	2022-06-06	272.06	274.18	252.53	249.5321	-6.31%	-16.8	2.12	0.78%	-19.53	-7.18%
78	2022-06-13	245.11	255.3	241.51	244.2651	-2.11%	-5.27	10.19	4.16%	-3.6	-1.47%
79	2022-06-20	250.26	267.98	249.51	264.041	8.10%	19.78	17.72	7.08%	-0.75	-0.30%
80	2022-06-27	268.21	268.3	252.9	256.032	-3.03%	-8.01	0.09	0.03%	-15.31	-5.71%
81	2022-07-04	256.16	269.06	254.74	264.0016	3.11%	7.97	12.9	5.04%	-1.42	-0.55%
82	2022-07-11	265.65	266.53	245.94	253.2111	-4.09%	-10.79	0.88	0.33%	-19.71	-7.42%
83	2022-07-18	259.75	265.33	253.3	256.8014	1.42%	3.59	5.58	2.15%	-6.45	-2.48%
84	2022-07-25	261	282	249.57	276.9027	7.83%	20.1	21	8.05%	-11.43	-4.38%
85	2022-08-01	277.82	283.8	272.38	279.0432	0.77%	2.14	5.98	2.15%	-5.44	-1.96%
86	2022-08-08	284.05	291.91	277.61	287.9201	3.18%	8.88	7.86	2.77%	-6.44	-2.27%
87	2022-08-15	291	294.18	285.56	282.2388	-1.97%	-5.68	3.18	1.09%	-5.44	-1.87%
88	2022-08-22	282.08	282.46	267.98	264.987	-6.11%	-17.25	0.38	0.13%	-14.1	-5.00%
89	2022-08-29	265.85	267.4	254.47	253.0962	-4.49%	-11.89	1.55	0.58%	-11.38	-4.28%
90	2022-09-05	256.2	265.23	251.94	261.399	3.28%	8.3	9.03	3.52%	-4.26	-1.66%
91	2022-09-12	265.78	267.45	242.06	241.9073	-7.46%	-19.49	1.67	0.63%	-23.72	-8.92%
92	2022-09-19	242.47	247.66	235.2	235.1662	-2.79%	-6.74	5.19	2.14%	-7.27	-3.00%
93	2022-09-26	237.05	242.33	232.73	230.2043	-2.11%	-4.96	5.28	2.23%	-4.32	-1.82%
94	2022-10-03	235.41	250.58	233.17	231.5288	0.58%	1.32	15.17	6.44%	-2.24	-0.95%
95	2022-10-10	233.05	237.24	219.13	225.9145	-2.42%	-5.61	4.19	1.80%	-13.92	-5.97%
96	2022-10-17	235.82	243.93	234.29	239.3176	5.93%	13.4	8.11	3.44%	-1.53	-0.65%
97	2022-10-24	243.76	251.04	225.78	233.1399	-2.58%	-6.18	7.28	2.99%	-17.98	-7.38%
98	2022-10-31	233.76	235.74	213.43	218.8275	-6.14%	-14.31	1.98	0.85%	-20.33	-8.70%
99	2022-11-07	221.99	247.99	221.28	244.2498	11.62%	25.42	26	11.71%	-0.71	-0.32%
100		274.18	280.43	266.59	267.59	-0.83%	-2.34	6.25	2.26%	-7.59	-2.75%
Sheet1

Cell Formulas
Range	Formula
B100:K100	B100	=SUM((DAY(($A2:$A99)+4)<=21)*(DAY(($A2:$A99)+4)>=15)*(WEEKDAY(($A2:$A99)+4,2)=5)*(B2:B99)) / SUM((DAY(($A2:$A99)+4)<=21)*(DAY(($A2:$A99)+4)>=15)*(WEEKDAY(($A2:$A99)+4,2)=5))

 

[15minoffame]

For the life of me, I can't figure out why I'm still getting the "Value" error since I copied your formula. I thought at first, it's the way I formatted the "Date" but I still get the same error when I changed it to your format.

Here's the formula: =SUM((DAY(($A2:$A99)+4)<=21)*(DAY(($A2:$A99)+4)>=15)*(WEEKDAY(($A2:$A99)+4,2)=5)*(B2:B99)) / SUM((DAY(($A2:$A99)+4)<=21)*(DAY(($A2:$A99)+4)>=15)*(WEEKDAY(($A2:$A99)+4,2)=5))

Date	Open	High	Low	Close	Adj Close	Volume	G/L %	Pts	Pts ABS	Up	W Streak	O-H	O-H %	O-L	O-L %
12/28/20​	224.45​	227.18​	219.68​	222.42​	216.6029​	76551100​						2.73		-4.77
1/4/21​	222.53​	223​	211.94​	219.62​	213.8761​	1.48E+08​	-1.26%	-2.73	2.73	0	0	0.47	0.21%	-10.59	-4.76%
1/11/21​	218.47​	218.91​	212.03​	212.65​	207.0884​	1.28E+08​	-3.17%	-6.79	6.79	0	0	0.44	0.20%	-6.44	-2.95%
1/18/21​	213.75​	230.07​	212.63​	225.95​	220.0406​	1.29E+08​	6.25%	12.95	12.95	1	1	16.32	7.64%	-1.12	-0.52%
1/25/21​	229.12​	242.64​	224.22​	231.96​	225.8934​	2.44E+08​	2.66%	5.85	5.85	1	2	13.52	5.90%	-4.90	-2.14%
2/1/21​	235.06​	245.09​	232.43​	242.2​	235.8656​	1.3E+08​	4.41%	9.97	9.97	1	3	10.03	4.27%	-2.63	-1.12%
2/8/21​	243.15​	245.92​	240.81​	244.99​	238.5826​	1E+08​	1.15%	2.72	2.72	1	4	2.77	1.14%	-2.34	-0.96%
2/15/21​	245.03​	246.13​	240.18​	240.97​	234.6677​	90570200​	-1.64%	-3.91	3.91	0	0	1.10	0.45%	-4.85	-1.98%
2/22/21​	237.42​	237.93​	227.88​	232.38​	226.8236​	1.7E+08​	-3.34%	-7.84	7.84	0	0	0.51	0.21%	-9.54	-4.02%
3/1/21​	235.9​	237.47​	224.26​	231.6​	226.0623​	1.69E+08​	-0.34%	-0.76	0.76	0	0	1.57	0.67%	-11.64	-4.93%
3/8/21​	231.37​	239.17​	227.13​	235.75​	230.1131​	1.51E+08​	1.79%	4.05	4.05	1	1	7.80	3.37%	-4.24	-1.83%
3/15/21​	234.96​	240.06​	229.35​	230.35​	224.8422​	1.65E+08​	-2.29%	-5.27	5.27	0	0	5.10	2.17%	-5.61	-2.39%
3/22/21​	230.27​	241.05​	230.14​	236.48​	230.8256​	1.47E+08​	2.66%	5.98	5.98	1	1	10.78	4.68%	-0.13	-0.06%
3/29/21​	236.59​	242.84​	231.1​	242.35​	236.5553​	1.24E+08​	2.48%	5.73	5.73	1	2	6.25	2.64%	-5.49	-2.32%
4/5/21​	242.76​	255.99​	242.7​	255.85​	249.7325​	1.31E+08​	5.57%	13.18	13.18	1	3	13.23	5.45%	-0.06	-0.02%
4/12/21​	254.71​	261​	254.62​	260.74​	254.5055​	1.25E+08​	1.91%	4.77	4.77	1	4	6.29	2.47%	-0.09	-0.04%
4/19/21​	260.19​	261.78​	255.64​	261.15​	254.9057​	1.14E+08​	0.16%	0.40	0.40	1	5	1.59	0.61%	-4.55	-1.75%
4/26/21​	261.66​	263.19​	249​	252.18​	246.1502​	1.69E+08​	-3.43%	-8.76	8.76	0	0	1.53	0.58%	-12.66	-4.84%
5/3/21​	253.4​	254.35​	244.69​	252.46​	246.4235​	1.28E+08​	0.11%	0.27	0.27	1	1	0.95	0.37%	-8.71	-3.44%
5/10/21​	250.87​	251.73​	238.07​	248.15​	242.2166​	1.53E+08​	-1.71%	-4.21	4.21	0	0	0.86	0.34%	-12.80	-5.10%
5/17/21​	246.55​	248.33​	238.6​	245.17​	239.3078​	1.15E+08​	-1.20%	-2.91	2.91	0	0	1.78	0.72%	-7.95	-3.22%
5/24/21​	247.79​	252.94​	247.51​	249.68​	244.2727​	99583800​	2.07%	4.96	4.96	1	1	5.15	2.08%	-0.28	-0.11%
5/31/21​	251.23​	251.65​	243​	250.79​	245.3587​	93208800​	0.44%	1.09	1.09	1	2	0.42	0.17%	-8.23	-3.28%
6/7/21​	249.98​	258.49​	249.81​	257.89​	252.3049​	1.07E+08​	2.83%	6.95	6.95	1	3	8.51	3.40%	-0.17	-0.07%
6/14/21​	257.9​	262.3​	254.42​	259.43​	253.8116​	1.29E+08​	0.60%	1.51	1.51	1	4	4.40	1.71%	-3.48	-1.35%
6/21/21​	259.82​	267.85​	257.92​	265.02​	259.2805​	1.18E+08​	2.15%	5.47	5.47	1	5	8.03	3.09%	-1.90	-0.73%
6/28/21​	266.19​	278​	265.91​	277.65​	271.637​	1.04E+08​	4.77%	12.36	12.36	1	6	11.81	4.44%	-0.28	-0.11%
7/5/21​	278.03​	280.69​	274.3​	277.94​	271.9207​	1.03E+08​	0.10%	0.28	0.28	1	7	2.66	0.96%	-3.73	-1.34%
7/12/21​	279.16​	284.1​	276.58​	280.75​	274.6699​	1.17E+08​	1.01%	2.75	2.75	1	8	4.94	1.77%	-2.58	-0.92%
7/19/21​	278.93​	289.99​	274.45​	289.67​	283.3967​	1.3E+08​	3.18%	8.73	8.73	1	9	11.06	3.97%	-4.48	-1.61%
7/26/21​	289​	290.15​	282.95​	284.91​	278.7398​	1.29E+08​	-1.64%	-4.66	4.66	0	0	1.15	0.40%	-6.05	-2.09%
8/2/21​	286.36​	289.63​	283.74​	289.46​	283.1912​	80827200​	1.60%	4.45	4.45	1	1	3.27	1.14%	-2.62	-0.91%
8/9/21​	289.75​	292.9​	285.2​	292.85​	286.5078​	81500400​	1.17%	3.32	3.32	1	2	3.15	1.09%	-4.55	-1.57%
8/16/21​	293.19​	305.84​	288.64​	304.36​	297.7685​	1.35E+08​	3.93%	11.26	11.26	1	3	12.65	4.31%	-4.55	-1.55%
8/23/21​	303.25​	305.65​	296.83​	299.72​	293.7904​	1.01E+08​	-1.34%	-3.98	3.98	0	0	2.40	0.79%	-6.42	-2.12%
8/30/21​	301.12​	305.19​	300.18​	301.14​	295.1823​	92650700​	0.47%	1.39	1.39	1	1	4.07	1.35%	-0.94	-0.31%
9/6/21​	301.01​	302.14​	295.38​	295.71​	289.8597​	71787600​	-1.80%	-5.32	5.32	0	0	1.13	0.38%	-5.63	-1.87%
9/13/21​	297.55​	305.32​	294.08​	299.87​	293.9374​	1.35E+08​	1.41%	4.08	4.08	1	1	7.77	2.61%	-3.47	-1.17%
9/20/21​	296.33​	300.9​	289.52​	299.35​	293.4277​	1.21E+08​	-0.17%	-0.51	0.51	0	0	4.57	1.54%	-6.81	-2.30%
9/27/21​	296.14​	296.47​	281.29​	289.1​	283.3805​	1.56E+08​	-3.42%	-10.05	10.05	0	0	0.33	0.11%	-14.85	-5.01%
10/4/21​	287.4​	296.64​	280.25​	294.85​	289.0168​	1.22E+08​	1.99%	5.64	5.64	1	1	9.24	3.22%	-7.15	-2.49%
10/11/21​	292.92​	304.45​	292.35​	304.21​	298.1915​	1.13E+08​	3.17%	9.17	9.17	1	2	11.53	3.94%	-0.57	-0.19%
10/18/21​	303.57​	311.09​	302.69​	309.16​	303.0436​	91315900​	1.63%	4.85	4.85	1	3	7.52	2.48%	-0.88	-0.29%
10/25/21​	309.36​	332​	306.46​	331.62​	325.0593​	1.59E+08​	7.26%	22.02	22.02	1	4	22.64	7.32%	-2.90	-0.94%
11/1/21​	331.36​	338.79​	326.37​	336.06​	329.4115​	1.22E+08​	1.34%	4.35	4.35	1	5	7.43	2.24%	-4.99	-1.51%
11/8/21​	337.3​	338.72​	329.92​	336.72​	330.0584​	1.08E+08​	0.20%	0.65	0.65	1	6	1.42	0.42%	-7.38	-2.19%
11/15/21​	337.54​	345.1​	334.03​	343.11​	336.3219​	1.01E+08​	1.90%	6.26	6.26	1	7	7.56	2.24%	-3.51	-1.04%
11/22/21​	344.62​	349.67​	328.12​	329.68​	323.7488​	1.07E+08​	-3.74%	-12.57	12.57	0	0	5.05	1.47%	-16.50	-4.79%
11/29/21​	334.94​	339.28​	318.03​	323.01​	317.1989​	1.77E+08​	-2.02%	-6.55	6.55	0	0	4.34	1.30%	-16.91	-5.05%
12/6/21​	323.95​	343​	319.23​	342.54​	336.3775​	1.46E+08​	6.05%	19.18	19.18	1	1	19.05	5.88%	-4.72	-1.46%
12/13/21​	340.68​	343.79​	317.25​	323.8​	317.9747​	1.92E+08​	-5.47%	-18.40	18.40	0	0	3.11	0.91%	-23.43	-6.88%
12/20/21​	320.05​	336.39​	317.57​	334.69​	328.6688​	97516400​	3.36%	10.69	10.69	1	1	16.34	5.11%	-2.48	-0.77%
12/27/21​	335.46​	344.3​	335.43​	336.32​	330.2694​	84645800​	0.49%	1.60	1.60	1	2	8.84	2.64%	-0.03	-0.01%
1/3/22​	335.35​	338​	310.09​	314.04​	308.3903​	1.74E+08​	-6.62%	-21.88	21.88	0	0	2.65	0.79%	-25.26	-7.53%
1/10/22​	309.49​	323.41​	303.75​	310.2​	304.6193​	1.93E+08​	-1.22%	-3.77	3.77	0	0	13.92	4.50%	-5.74	-1.85%
1/17/22​	304.07​	313.91​	295.61​	296.03​	290.7043​	1.82E+08​	-4.57%	-13.92	13.92	0	0	9.84	3.24%	-8.46	-2.78%
1/24/22​	292.2​	308.5​	276.05​	308.26​	302.7143​	3.52E+08​	4.13%	12.01	12.01	1	1	16.30	5.58%	-16.15	-5.53%
1/31/22​	308.95​	315.12​	299.96​	305.94​	300.436​	2.03E+08​	-0.75%	-2.28	2.28	0	0	6.17	2.00%	-8.99	-2.91%
2/7/22​	306.17​	311.93​	294.22​	295.04​	289.7321​	1.77E+08​	-3.56%	-10.70	10.70	0	0	5.76	1.88%	-11.95	-3.90%
2/14/22​	293.77​	300.87​	286.31​	287.93​	282.7499​	1.6E+08​	-2.41%	-6.98	6.98	0	0	7.10	2.42%	-7.46	-2.54%
2/21/22​	285​	297.63​	271.52​	297.31​	292.5649​	1.69E+08​	3.47%	9.81	9.81	1	1	12.63	4.43%	-13.48	-4.73%
2/28/22​	294.31​	303.13​	287.17​	289.86​	285.2338​	1.57E+08​	-2.51%	-7.33	7.33	0	0	8.82	3.00%	-7.14	-2.43%
3/7/22​	288.53​	289.69​	270​	280.07​	275.6001​	1.84E+08​	-3.38%	-9.63	9.63	0	0	1.16	0.40%	-18.53	-6.42%
3/14/22​	280.34​	301​	275.82​	300.43​	295.6351​	1.77E+08​	7.27%	20.04	20.04	1	1	20.66	7.37%	-4.52	-1.61%
3/21/22​	298.89​	305.5​	294.9​	303.68​	298.8333​	1.29E+08​	1.08%	3.20	3.20	1	2	6.61	2.21%	-3.99	-1.33%
3/28/22​	304.33​	315.95​	304.33​	309.42​	304.4817​	1.49E+08​	1.89%	5.65	5.65	1	3	11.62	3.82%	0.00	0.00%
4/4/22​	310.09​	315.11​	296.28​	296.97​	292.2304​	1.43E+08​	-4.02%	-12.25	12.25	0	0	5.02	1.62%	-13.81	-4.45%
4/11/22​	291.79​	292.61​	279.32​	279.83​	275.3639​	1.16E+08​	-5.77%	-16.87	16.87	0	0	0.82	0.28%	-12.47	-4.27%
4/18/22​	278.91​	293.3​	273.38​	274.03​	269.6565​	1.25E+08​	-2.07%	-5.71	5.71	0	0	14.39	5.16%	-5.53	-1.98%
4/25/22​	273.29​	290.98​	270​	277.52​	273.0908​	2.16E+08​	1.27%	3.43	3.43	1	1	17.69	6.47%	-3.29	-1.20%
5/2/22​	277.71​	290.88​	271.27​	274.73​	270.3453​	1.76E+08​	-1.01%	-2.75	2.75	0	0	13.17	4.74%	-6.44	-2.32%
5/9/22​	270.06​	273.75​	250.02​	261.12​	256.9525​	2.22E+08​	-4.95%	-13.39	13.39	0	0	3.69	1.37%	-20.04	-7.42%
5/16/22​	259.96​	268.33​	246.44​	252.56​	248.5291​	1.65E+08​	-3.28%	-8.42	8.42	0	0	8.37	3.22%	-13.52	-5.20%
5/23/22​	255.49​	273.34​	253.43​	273.24​	269.5053​	1.43E+08​	8.44%	20.98	20.98	1	1	17.85	6.99%	-2.06	-0.81%
5/30/22​	272.53​	277.69​	261.6​	270.02​	266.3293​	1.35E+08​	-1.18%	-3.18	3.18	0	0	5.16	1.89%	-10.93	-4.01%
6/6/22​	272.06​	274.18​	252.53​	252.99​	249.5321​	1.2E+08​	-6.31%	-16.80	16.80	0	0	2.12	0.78%	-19.53	-7.18%
6/13/22​	245.11​	255.3​	241.51​	247.65​	244.2651​	1.84E+08​	-2.11%	-5.27	5.27	0	0	10.19	4.16%	-3.60	-1.47%
6/20/22​	250.26​	267.98​	249.51​	267.7​	264.041​	1.16E+08​	8.10%	19.78	19.78	1	1	17.72	7.08%	-0.75	-0.30%
6/27/22​	268.21​	268.3​	252.9​	259.58​	256.032​	1.27E+08​	-3.03%	-8.01	8.01	0	0	0.09	0.03%	-15.31	-5.71%
7/4/22​	256.16​	269.06​	254.74​	267.66​	264.0016​	87284100​	3.11%	7.97	7.97	1	1	12.90	5.04%	-1.42	-0.55%
7/11/22​	265.65​	266.53​	245.94​	256.72​	253.2111​	1.4E+08​	-4.09%	-10.79	10.79	0	0	0.88	0.33%	-19.71	-7.42%
7/18/22​	259.75​	265.33​	253.3​	260.36​	256.8014​	1.13E+08​	1.42%	3.59	3.59	1	1	5.58	2.15%	-6.45	-2.48%
7/25/22​	261​	282​	249.57​	280.74​	276.9027​	1.72E+08​	7.83%	20.10	20.10	1	2	21.00	8.05%	-11.43	-4.38%
8/1/22​	277.82​	283.8​	272.38​	282.91​	279.0432​	1.03E+08​	0.77%	2.14	2.14	1	3	5.98	2.15%	-5.44	-1.96%
8/8/22​	284.05​	291.91​	277.61​	291.91​	287.9201​	1.1E+08​	3.18%	8.88	8.88	1	4	7.86	2.77%	-6.44	-2.27%
8/15/22​	291​	294.18​	285.56​	286.15​	282.2388​	92198200​	-1.97%	-5.68	5.68	0	0	3.18	1.09%	-5.44	-1.87%
8/22/22​	282.08​	282.46​	267.98​	268.09​	264.987​	1.05E+08​	-6.11%	-17.25	17.25	0	0	0.38	0.13%	-14.10	-5.00%
8/29/22​	265.85​	267.4​	254.47​	256.06​	253.0962​	1.14E+08​	-4.49%	-11.89	11.89	0	0	1.55	0.58%	-11.38	-4.28%
9/5/22​	256.2​	265.23​	251.94​	264.46​	261.399​	87859500​	3.28%	8.30	8.30	1	1	9.03	3.52%	-4.26	-1.66%
9/12/22​	265.78​	267.45​	242.06​	244.74​	241.9073​	1.47E+08​	-7.46%	-19.49	19.49	0	0	1.67	0.63%	-23.72	-8.92%
9/19/22​	242.47​	247.66​	235.2​	237.92​	235.1662​	1.47E+08​	-2.79%	-6.74	6.74	0	0	5.19	2.14%	-7.27	-3.00%
9/26/22​	237.05​	242.33​	232.73​	232.9​	230.2043​	1.47E+08​	-2.11%	-4.96	4.96	0	0	5.28	2.23%	-4.32	-1.82%
10/3/22​	235.41​	250.58​	233.17​	234.24​	231.5288​	1.42E+08​	0.58%	1.32	1.32	1	1	15.17	6.44%	-2.24	-0.95%
10/10/22​	233.05​	237.24​	219.13​	228.56​	225.9145​	1.55E+08​	-2.42%	-5.61	5.61	0	0	4.19	1.80%	-13.92	-5.97%
10/17/22​	235.82​	243.93​	234.29​	242.12​	239.3176​	1.23E+08​	5.93%	13.40	13.40	1	1	8.11	3.44%	-1.53	-0.65%
10/24/22​	243.76​	251.04​	225.78​	235.87​	233.1399​	2.23E+08​	-2.58%	-6.18	6.18	0	0	7.28	2.99%	-17.98	-7.38%
10/31/22​	233.76​	235.74​	213.43​	221.39​	218.8275​	1.71E+08​	-6.14%	-14.31	14.31	0	0	1.98	0.85%	-20.33	-8.70%
11/7/22​	221.99​	247.99​	221.28​	247.11​	244.2498​	1.7E+08​	11.62%	25.42	25.42	1	1	26.00	11.71%	-0.71	-0.32%
	#VALUE!​	#VALUE!​	#VALUE!​	#VALUE!​	#VALUE!​	#VALUE!​	#VALUE!​	#VALUE!​	#VALUE!​	#VALUE!​	#VALUE!​	#VALUE!​	#VALUE!​	#VALUE!​	#VALUE!​

 

[awoohaw]

Posting a table really does not help me debug.

Try posting the solution I gave you in a BRAND NEW WORKBOOK. Do not change a thing. If the error still persists, then you really need to post the formulas and column and row heading.

 

[15minoffame]

It's still giving me the "Value" error in a new workbook. I downloaded XL2BB but is says "this file is not supported in protected view". I was hoping I could get it to you to troubleshoot it. I have the formula in B100.

 

[awoohaw]

One suggestion that may work since you don't say what version of excel you use (Nevermind, you answered this earlier, but please update your profile with this so the forum doesn't have to ask you).
Change all of the SUM functions to SUMPRODUCT.

I have done this on the xl2bb mini sheet below.

If that doesn't work, again....

Please post the formula so it can be copied. If there is an error in your formula I could reverse error and get it to work and not know i changed your formula.

You have not addressed the calculation of the values in columns F:K, are my guesses correct? Shouldn't the same formula be used for your thrice weekly averages insteaded of averaging of percentages?

You also seem to have columns in your workbook now that were not in the other. Yet you do not post an image of the mini worksheet I asked you to?
Please don't change your scenario and expect the forum to just figure it out.

Please do what I asked in Post #7. Do not post an image. Post a table if you can't use xl2bb.
... put column and row headers (letters and numbers) on the table.
.... copy and paste the FORMULAS for all the errors you have, and tell what cell the formula is in.

Make no changes, insert no columns.
Do not change formulas.

Thanks in advance.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Open	High	Low	Close	G/L %	Pts	O-H	O-H %	O-L	O-L %
2	2020-12-28	224.45	227.18	219.68	216.6029			2.73		-4.77
3	2021-01-04	222.53	223.00	211.94	213.8761	-1.26%	-2.73	0.47	0.21%	-10.59	-4.76%
4	2021-01-11	218.47	218.91	212.03	207.0884	-3.17%	-6.79	0.44	0.20%	-6.44	-2.95%
5	2021-01-18	213.75	230.07	212.63	220.0406	6.25%	12.95	16.32	7.64%	-1.12	-0.52%
6	2021-01-25	229.12	242.64	224.22	225.8934	2.66%	5.85	13.52	5.90%	-4.90	-2.14%
7	2021-02-01	235.06	245.09	232.43	235.8656	4.41%	9.97	10.03	4.27%	-2.63	-1.12%
8	2021-02-08	243.15	245.92	240.81	238.5826	1.15%	2.72	2.77	1.14%	-2.34	-0.96%
9	2021-02-15	245.03	246.13	240.18	234.6677	-1.64%	-3.91	1.10	0.45%	-4.85	-1.98%
10	2021-02-22	237.42	237.93	227.88	226.8236	-3.34%	-7.84	0.51	0.21%	-9.54	-4.02%
11	2021-03-01	235.90	237.47	224.26	226.0623	-0.34%	-0.76	1.57	0.67%	-11.64	-4.93%
12	2021-03-08	231.37	239.17	227.13	230.1131	1.79%	4.05	7.80	3.37%	-4.24	-1.83%
13	2021-03-15	234.96	240.06	229.35	224.8422	-2.29%	-5.27	5.10	2.17%	-5.61	-2.39%
14	2021-03-22	230.27	241.05	230.14	230.8256	2.66%	5.98	10.78	4.68%	-0.13	-0.06%
15	2021-03-29	236.59	242.84	231.10	236.5553	2.48%	5.73	6.25	2.64%	-5.49	-2.32%
16	2021-04-05	242.76	255.99	242.70	249.7325	5.57%	13.18	13.23	5.45%	-0.06	-0.02%
17	2021-04-12	254.71	261.00	254.62	254.5055	1.91%	4.77	6.29	2.47%	-0.09	-0.04%
18	2021-04-19	260.19	261.78	255.64	254.9057	0.16%	0.40	1.59	0.61%	-4.55	-1.75%
19	2021-04-26	261.66	263.19	249.00	246.1502	-3.43%	-8.76	1.53	0.58%	-12.66	-4.84%
20	2021-05-03	253.40	254.35	244.69	246.4235	0.11%	0.27	0.95	0.37%	-8.71	-3.44%
21	2021-05-10	250.87	251.73	238.07	242.2166	-1.71%	-4.21	0.86	0.34%	-12.80	-5.10%
22	2021-05-17	246.55	248.33	238.60	239.3078	-1.20%	-2.91	1.78	0.72%	-7.95	-3.22%
23	2021-05-24	247.79	252.94	247.51	244.2727	2.07%	4.96	5.15	2.08%	-0.28	-0.11%
24	2021-05-31	251.23	251.65	243.00	245.3587	0.44%	1.09	0.42	0.17%	-8.23	-3.28%
25	2021-06-07	249.98	258.49	249.81	252.3049	2.83%	6.95	8.51	3.40%	-0.17	-0.07%
26	2021-06-14	257.90	262.30	254.42	253.8116	0.60%	1.51	4.40	1.71%	-3.48	-1.35%
27	2021-06-21	259.82	267.85	257.92	259.2805	2.15%	5.47	8.03	3.09%	-1.90	-0.73%
28	2021-06-28	266.19	278.00	265.91	271.6370	4.77%	12.36	11.81	4.44%	-0.28	-0.11%
29	2021-07-05	278.03	280.69	274.30	271.9207	0.10%	0.28	2.66	0.96%	-3.73	-1.34%
30	2021-07-12	279.16	284.10	276.58	274.6699	1.01%	2.75	4.94	1.77%	-2.58	-0.92%
31	2021-07-19	278.93	289.99	274.45	283.3967	3.18%	8.73	11.06	3.97%	-4.48	-1.61%
32	2021-07-26	289.00	290.15	282.95	278.7398	-1.64%	-4.66	1.15	0.40%	-6.05	-2.09%
33	2021-08-02	286.36	289.63	283.74	283.1912	1.60%	4.45	3.27	1.14%	-2.62	-0.91%
34	2021-08-09	289.75	292.90	285.20	286.5078	1.17%	3.32	3.15	1.09%	-4.55	-1.57%
35	2021-08-16	293.19	305.84	288.64	297.7685	3.93%	11.26	12.65	4.31%	-4.55	-1.55%
36	2021-08-23	303.25	305.65	296.83	293.7904	-1.34%	-3.98	2.40	0.79%	-6.42	-2.12%
37	2021-08-30	301.12	305.19	300.18	295.1823	0.47%	1.39	4.07	1.35%	-0.94	-0.31%
38	2021-09-06	301.01	302.14	295.38	289.8597	-1.80%	-5.32	1.13	0.38%	-5.63	-1.87%
39	2021-09-13	297.55	305.32	294.08	293.9374	1.41%	4.08	7.77	2.61%	-3.47	-1.17%
40	2021-09-20	296.33	300.90	289.52	293.4277	-0.17%	-0.51	4.57	1.54%	-6.81	-2.30%
41	2021-09-27	296.14	296.47	281.29	283.3805	-3.42%	-10.05	0.33	0.11%	-14.85	-5.01%
42	2021-10-04	287.40	296.64	280.25	289.0168	1.99%	5.64	9.24	3.22%	-7.15	-2.49%
43	2021-10-11	292.92	304.45	292.35	298.1915	3.17%	9.17	11.53	3.94%	-0.57	-0.19%
44	2021-10-18	303.57	311.09	302.69	303.0436	1.63%	4.85	7.52	2.48%	-0.88	-0.29%
45	2021-10-25	309.36	332.00	306.46	325.0593	7.26%	22.02	22.64	7.32%	-2.90	-0.94%
46	2021-11-01	331.36	338.79	326.37	329.4115	1.34%	4.35	7.43	2.24%	-4.99	-1.51%
47	2021-11-08	337.30	338.72	329.92	330.0584	0.20%	0.65	1.42	0.42%	-7.38	-2.19%
48	2021-11-15	337.54	345.10	334.03	336.3219	1.90%	6.26	7.56	2.24%	-3.51	-1.04%
49	2021-11-22	344.62	349.67	328.12	323.7488	-3.74%	-12.57	5.05	1.47%	-16.50	-4.79%
50	2021-11-29	334.94	339.28	318.03	317.1989	-2.02%	-6.55	4.34	1.30%	-16.91	-5.05%
51	2021-12-06	323.95	343.00	319.23	336.3775	6.05%	19.18	19.05	5.88%	-4.72	-1.46%
52	2021-12-13	340.68	343.79	317.25	317.9747	-5.47%	-18.40	3.11	0.91%	-23.43	-6.88%
53	2021-12-20	320.05	336.39	317.57	328.6688	3.36%	10.69	16.34	5.11%	-2.48	-0.77%
54	2021-12-27	335.46	344.30	335.43	330.2694	0.49%	1.60	8.84	2.64%	-0.03	-0.01%
55	2022-01-03	335.35	338.00	310.09	308.3903	-6.62%	-21.88	2.65	0.79%	-25.26	-7.53%
56	2022-01-10	309.49	323.41	303.75	304.6193	-1.22%	-3.77	13.92	4.50%	-5.74	-1.85%
57	2022-01-17	304.07	313.91	295.61	290.7043	-4.57%	-13.92	9.84	3.24%	-8.46	-2.78%
58	2022-01-24	292.20	308.50	276.05	302.7143	4.13%	12.01	16.30	5.58%	-16.15	-5.53%
59	2022-01-31	308.95	315.12	299.96	300.4360	-0.75%	-2.28	6.17	2.00%	-8.99	-2.91%
60	2022-02-07	306.17	311.93	294.22	289.7321	-3.56%	-10.70	5.76	1.88%	-11.95	-3.90%
61	2022-02-14	293.77	300.87	286.31	282.7499	-2.41%	-6.98	7.10	2.42%	-7.46	-2.54%
62	2022-02-21	285.00	297.63	271.52	292.5649	3.47%	9.81	12.63	4.43%	-13.48	-4.73%
63	2022-02-28	294.31	303.13	287.17	285.2338	-2.51%	-7.33	8.82	3.00%	-7.14	-2.43%
64	2022-03-07	288.53	289.69	270.00	275.6001	-3.38%	-9.63	1.16	0.40%	-18.53	-6.42%
65	2022-03-14	280.34	301.00	275.82	295.6351	7.27%	20.04	20.66	7.37%	-4.52	-1.61%
66	2022-03-21	298.89	305.50	294.90	298.8333	1.08%	3.20	6.61	2.21%	-3.99	-1.33%
67	2022-03-28	304.33	315.95	304.33	304.4817	1.89%	5.65	11.62	3.82%	0.00	0.00%
68	2022-04-04	310.09	315.11	296.28	292.2304	-4.02%	-12.25	5.02	1.62%	-13.81	-4.45%
69	2022-04-11	291.79	292.61	279.32	275.3639	-5.77%	-16.87	0.82	0.28%	-12.47	-4.27%
70	2022-04-18	278.91	293.30	273.38	269.6565	-2.07%	-5.71	14.39	5.16%	-5.53	-1.98%
71	2022-04-25	273.29	290.98	270.00	273.0908	1.27%	3.43	17.69	6.47%	-3.29	-1.20%
72	2022-05-02	277.71	290.88	271.27	270.3453	-1.01%	-2.75	13.17	4.74%	-6.44	-2.32%
73	2022-05-09	270.06	273.75	250.02	256.9525	-4.95%	-13.39	3.69	1.37%	-20.04	-7.42%
74	2022-05-16	259.96	268.33	246.44	248.5291	-3.28%	-8.42	8.37	3.22%	-13.52	-5.20%
75	2022-05-23	255.49	273.34	253.43	269.5053	8.44%	20.98	17.85	6.99%	-2.06	-0.81%
76	2022-05-30	272.53	277.69	261.60	266.3293	-1.18%	-3.18	5.16	1.89%	-10.93	-4.01%
77	2022-06-06	272.06	274.18	252.53	249.5321	-6.31%	-16.80	2.12	0.78%	-19.53	-7.18%
78	2022-06-13	245.11	255.30	241.51	244.2651	-2.11%	-5.27	10.19	4.16%	-3.60	-1.47%
79	2022-06-20	250.26	267.98	249.51	264.0410	8.10%	19.78	17.72	7.08%	-0.75	-0.30%
80	2022-06-27	268.21	268.30	252.90	256.0320	-3.03%	-8.01	0.09	0.03%	-15.31	-5.71%
81	2022-07-04	256.16	269.06	254.74	264.0016	3.11%	7.97	12.90	5.04%	-1.42	-0.55%
82	2022-07-11	265.65	266.53	245.94	253.2111	-4.09%	-10.79	0.88	0.33%	-19.71	-7.42%
83	2022-07-18	259.75	265.33	253.30	256.8014	1.42%	3.59	5.58	2.15%	-6.45	-2.48%
84	2022-07-25	261.00	282.00	249.57	276.9027	7.83%	20.10	21.00	8.05%	-11.43	-4.38%
85	2022-08-01	277.82	283.80	272.38	279.0432	0.77%	2.14	5.98	2.15%	-5.44	-1.96%
86	2022-08-08	284.05	291.91	277.61	287.9201	3.18%	8.88	7.86	2.77%	-6.44	-2.27%
87	2022-08-15	291.00	294.18	285.56	282.2388	-1.97%	-5.68	3.18	1.09%	-5.44	-1.87%
88	2022-08-22	282.08	282.46	267.98	264.9870	-6.11%	-17.25	0.38	0.13%	-14.10	-5.00%
89	2022-08-29	265.85	267.40	254.47	253.0962	-4.49%	-11.89	1.55	0.58%	-11.38	-4.28%
90	2022-09-05	256.20	265.23	251.94	261.3990	3.28%	8.30	9.03	3.52%	-4.26	-1.66%
91	2022-09-12	265.78	267.45	242.06	241.9073	-7.46%	-19.49	1.67	0.63%	-23.72	-8.92%
92	2022-09-19	242.47	247.66	235.20	235.1662	-2.79%	-6.74	5.19	2.14%	-7.27	-3.00%
93	2022-09-26	237.05	242.33	232.73	230.2043	-2.11%	-4.96	5.28	2.23%	-4.32	-1.82%
94	2022-10-03	235.41	250.58	233.17	231.5288	0.58%	1.32	15.17	6.44%	-2.24	-0.95%
95	2022-10-10	233.05	237.24	219.13	225.9145	-2.42%	-5.61	4.19	1.80%	-13.92	-5.97%
96	2022-10-17	235.82	243.93	234.29	239.3176	5.93%	13.40	8.11	3.44%	-1.53	-0.65%
97	2022-10-24	243.76	251.04	225.78	233.1399	-2.58%	-6.18	7.28	2.99%	-17.98	-7.38%
98	2022-10-31	233.76	235.74	213.43	218.8275	-6.14%	-14.31	1.98	0.85%	-20.33	-8.70%
99	2022-11-07	221.99	247.99	221.28	244.2498	11.62%	25.42	26.00	11.71%	-0.71	-0.32%
100		274.18	280.43	266.59	267.5913	-0.83%	-2.34	6.25	2.26%	-7.59	-2.75%
Sheet1

Cell Formulas
Range	Formula
B100:K100	B100	=SUMPRODUCT((DAY(($A2:$A99)+4)<=21)*(DAY(($A2:$A99)+4)>=15)*(WEEKDAY(($A2:$A99)+4,2)=5)*(B2:B99)) / SUMPRODUCT((DAY(($A2:$A99)+4)<=21)*(DAY(($A2:$A99)+4)>=15)*(WEEKDAY(($A2:$A99)+4,2)=5))

 

[awoohaw]

I downloaded XL2BB but is says "this file is not supported in protected view". I was hoping I could get it to you to troubleshoot it.

in my footer is a link to the xl2bb testing area for the forum. That is the only thing I can tell you. But, you may need to look in your OPTIONS and enable certain add ins that you have saved into your trusted locations. I am not good at troubleshooting how add ins work.