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%

 

[15minoffame]

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))

The Sumproduct works! Thank you so much! What would I need to change in the formula for the 1st, 2nd, and 4th Friday of every month?

 

[awoohaw]

Okay, I'm happy I thought of that. And as I asked, please update your profile to reflect your excel version. the SUM array functionality doesn't seem to work in 2016, but it does in 365 (your version vs mine). You also could have had it work with the SUM function by pressing "CNTL-SHFT-ENTR" (commonly called CSE keystroke) instead of just ENTER when committing the formulas in a cell.

to select the different Fridays change the 15 and 21 parts of the formula to what ever possible dates are for the Fridays you seek:
First Fridays can only be 1st through 7th,
Second can only be 8th through 14th
I'll let you guess the Fourth Friday ranges.
This is the part of each formula you need to change. You can change all with the REPLACE -CNTRL-H tool. But, be sure not change any cell references that have identical numbers, so the change value should include the text before and/or after ... like change "<=21" to "<=14"... etc:

(DAY(($A2:$A99)+4)<=21)*(DAY(($A2:$A99)+4)>=15)

The 4 in the formula depends on the day of the week your "date" column is in your calendar. Since you have Mondays, Fridays are 4 days later. If for some reason you changed that date to Wednesdays you would need to change all the 4's to 2. The formula will not work if you have mixed weekdays in that column. (but you "COULD" update the formula with a calculation to account for that - but I'm not going to do that. Maybe you can think on it if you're curious).

Best wishes!

 

[15minoffame]

Okay, I'm happy I thought of that. And as I asked, please update your profile to reflect your excel version. the SUM array functionality doesn't seem to work in 2016, but it does in 365 (your version vs mine). You also could have had it work with the SUM function by pressing "CNTL-SHFT-ENTR" (commonly called CSE keystroke) instead of just ENTER when committing the formulas in a cell.

to select the different Fridays change the 15 and 21 parts of the formula to what ever possible dates are for the Fridays you seek:
First Fridays can only be 1st through 7th,
Second can only be 8th through 14th
I'll let you guess the Fourth Friday ranges.
This is the part of each formula you need to change. You can change all with the REPLACE -CNTRL-H tool. But, be sure not change any cell references that have identical numbers, so the change value should include the text before and/or after ... like change "<=21" to "<=14"... etc:

(DAY(($A2:$A99)+4)<=21)*(DAY(($A2:$A99)+4)>=15)

The 4 in the formula depends on the day of the week your "date" column is in your calendar. Since you have Mondays, Fridays are 4 days later. If for some reason you changed that date to Wednesdays you would need to change all the 4's to 2. The formula will not work if you have mixed weekdays in that column. (but you "COULD" update the formula with a calculation to account for that - but I'm not going to do that. Maybe you can think on it if you're curious).

Best wishes!

I went to my profile, but couldn't see where I can add my excel version so how do I do this?

 

[15minoffame]

Could you please help me with the second part of the initial question. Is there a way to get the average between winning & losing weeks? In other words, separate these two weeks and then get the average.

 

[15minoffame]

Can anyone else help me in getting the average & Win / Loss count between the green & red weeks of the monthly option expiration weeks over the past three years? For those of you who are not familiar with stocks, every third Friday of every month is known as monthly option expiration week. This is the most important week due to volatility so it's an advantage to have stats to show the average High > Open and Low < Open to determine how to structure options buying. I have Excel 2016.

Thank you!