Average of income for the last 6 months (rolling) excluding weekends

[Carlos5]

Hello all, I did search but I can't find anything that helps me so here goes:
I am using Excel 2019. Column A has the date, Column B has the day (Mon, Tues, etc), and column C has the total $ made for that day. I am trying to write a formula to give me the average of the last 6 months excluding Sat and Sun. I assume I could use the Column B (the Day column) to exclude Sat and Sun. and use the TODAY() function -180 days to get the time frame but I can't seem to put the two togther.
Thanks for any help you can give me.

 

[awoohaw]

If you have LET functions this can probably be done a little easier. but try this:
(you do not need a column for day of week, unless you want it in a separate column, this formula reads the weekday number from the date column)

Book1
	A	B	C	D
1	Date		Earnings	Last 6 Months
2	Sun 2023-01-01		67
3	Mon 2023-01-02		111	111.00
4	Tue 2023-01-03		89	100.00
5	Wed 2023-01-04		92	97.33
6	Thu 2023-01-05		55	86.75
7	Fri 2023-01-06		92	87.80
8	Sat 2023-01-07		104	87.80
9	Sun 2023-01-08		120	87.80
10	Mon 2023-01-09		72	85.17
11	Tue 2023-01-10		66	82.43
12	Wed 2023-01-11		53	78.75
13	Thu 2023-01-12		103	81.44
14	Fri 2023-01-13		72	80.50
15	Sat 2023-01-14		118	80.50
16	Sun 2023-01-15		114	80.50
17	Mon 2023-01-16		121	84.18
18	Tue 2023-01-17		51	81.42
19	Wed 2023-01-18		125	84.77
20	Thu 2023-01-19		82	84.57
21	Fri 2023-01-20		97	85.40
22	Sat 2023-01-21		108	85.40
23	Sun 2023-01-22		114	85.40
24	Mon 2023-01-23		115	87.25
25	Tue 2023-01-24		78	86.71
26	Wed 2023-01-25		77	86.17
27	Thu 2023-01-26		95	86.63
28	Fri 2023-01-27		102	87.40
29	Sat 2023-01-28		72	87.40
30	Sun 2023-01-29		114	87.40
31	Mon 2023-01-30		122	89.05
32	Tue 2023-01-31		57	87.59
33	Wed 2023-02-01		77	87.13
34	Thu 2023-02-02		105	87.88
35	Fri 2023-02-03		88	87.88
36	Sat 2023-02-04		95	87.88
37	Sun 2023-02-05		70	87.88
38	Mon 2023-02-06		89	87.92
39	Tue 2023-02-07		95	88.19
40	Wed 2023-02-08		110	88.96
41	Thu 2023-02-09		112	89.76
42	Fri 2023-02-10		87	89.67
43	Sat 2023-02-11		70	89.67
44	Sun 2023-02-12		63	89.67
45	Mon 2023-02-13		116	90.52
46	Tue 2023-02-14		74	90.00
47	Wed 2023-02-15		74	89.52
48	Thu 2023-02-16		110	90.12
49	Fri 2023-02-17		66	89.43
50	Sat 2023-02-18		93	89.43
51	Sun 2023-02-19		72	89.43
52	Mon 2023-02-20		60	88.61
53	Tue 2023-02-21		80	88.38
54	Wed 2023-02-22		101	88.71
55	Thu 2023-02-23		58	87.92
56	Fri 2023-02-24		79	87.70
57	Sat 2023-02-25		84	87.70
58	Sun 2023-02-26		55	87.70
59	Mon 2023-02-27		64	87.12
60	Tue 2023-02-28		103	87.50
61	Wed 2023-03-01		113	88.09
62	Thu 2023-03-02		122	88.86
63	Fri 2023-03-03		66	88.36
64	Sat 2023-03-04		112	88.36
65	Sun 2023-03-05		93	88.36
66	Mon 2023-03-06		108	88.78
67	Tue 2023-03-07		90	88.81
68	Wed 2023-03-08		62	88.25
69	Thu 2023-03-09		53	87.53
70	Fri 2023-03-10		62	87.02
71	Sat 2023-03-11		87	87.02
72	Sun 2023-03-12		63	87.02
73	Mon 2023-03-13		59	86.47
74	Tue 2023-03-14		102	86.77
75	Wed 2023-03-15		115	87.30
76	Thu 2023-03-16		109	87.70
77	Fri 2023-03-17		77	87.51
78	Sat 2023-03-18		80	87.51
79	Sun 2023-03-19		79	87.51
80	Mon 2023-03-20		69	87.18
81	Tue 2023-03-21		121	87.77
82	Wed 2023-03-22		91	87.83
83	Thu 2023-03-23		57	87.31
84	Fri 2023-03-24		75	87.10
85	Sat 2023-03-25		100	87.10
86	Sun 2023-03-26		117	87.10
87	Mon 2023-03-27		125	87.72
88	Tue 2023-03-28		101	87.94
89	Wed 2023-03-29		83	87.86
90	Thu 2023-03-30		71	87.59
91	Fri 2023-03-31		71	87.34
92	Sat 2023-04-01		123	87.34
93	Sun 2023-04-02		118	87.34
94	Mon 2023-04-03		60	86.92
95	Tue 2023-04-04		68	86.64
96	Wed 2023-04-05		104	86.90
97	Thu 2023-04-06		72	86.68
98	Fri 2023-04-07		70	86.44
99	Sat 2023-04-08		104	86.44
100	Sun 2023-04-09		76	86.44
101	Mon 2023-04-10		74	86.27
102	Tue 2023-04-11		97	86.42
103	Wed 2023-04-12		50	85.92
104	Thu 2023-04-13		52	85.46
105	Fri 2023-04-14		59	85.11
106	Sat 2023-04-15		106	85.11
107	Sun 2023-04-16		57	85.11
108	Mon 2023-04-17		53	84.68
109	Tue 2023-04-18		114	85.06
110	Wed 2023-04-19		57	84.71
111	Thu 2023-04-20		102	84.92
112	Fri 2023-04-21		61	84.63
113	Sat 2023-04-22		107	84.63
114	Sun 2023-04-23		66	84.63
115	Mon 2023-04-24		92	84.72
116	Tue 2023-04-25		101	84.91
117	Wed 2023-04-26		87	84.94
118	Thu 2023-04-27		58	84.62
119	Fri 2023-04-28		121	85.05
120	Sat 2023-04-29		67	85.05
121	Sun 2023-04-30		85	85.05
122	Mon 2023-05-01		121	85.47
123	Tue 2023-05-02		121	85.87
124	Wed 2023-05-03		100	86.03
125	Thu 2023-05-04		110	86.30
126	Fri 2023-05-05		115	86.62
127	Sat 2023-05-06		102	86.62
128	Sun 2023-05-07		53	86.62
129	Mon 2023-05-08		53	86.25
130	Tue 2023-05-09		55	85.91
131	Wed 2023-05-10		112	86.19
132	Thu 2023-05-11		118	86.53
133	Fri 2023-05-12		92	86.59
134	Sat 2023-05-13		101	86.59
135	Sun 2023-05-14		61	86.59
136	Mon 2023-05-15		89	86.61
137	Tue 2023-05-16		66	86.40
138	Wed 2023-05-17		76	86.30
139	Thu 2023-05-18		77	86.20
140	Fri 2023-05-19		74	86.08
141	Sat 2023-05-20		97	86.08
142	Sun 2023-05-21		105	86.08
143	Mon 2023-05-22		109	86.31
144	Tue 2023-05-23		78	86.23
145	Wed 2023-05-24		115	86.50
146	Thu 2023-05-25		70	86.35
147	Fri 2023-05-26		67	86.16
148	Sat 2023-05-27		55	86.16
149	Sun 2023-05-28		78	86.16
150	Mon 2023-05-29		87	86.17
151	Tue 2023-05-30		110	86.39
152	Wed 2023-05-31		123	86.73
153	Thu 2023-06-01		75	86.62
154	Fri 2023-06-02		83	86.59
155	Sat 2023-06-03		96	86.59
156	Sun 2023-06-04		77	86.59
157	Mon 2023-06-05		109	86.79
158	Tue 2023-06-06		112	87.02
159	Wed 2023-06-07		70	86.87
160	Thu 2023-06-08		83	86.83
161	Fri 2023-06-09		117	87.10
162	Sat 2023-06-10		124	87.10
163	Sun 2023-06-11		61	87.10
164	Mon 2023-06-12		63	86.89
165	Tue 2023-06-13		107	87.06
166	Wed 2023-06-14		63	86.86
167	Thu 2023-06-15		113	87.08
168	Fri 2023-06-16		83	87.04
169	Sat 2023-06-17		95	87.04
170	Sun 2023-06-18		108	87.04
171	Mon 2023-06-19		125	87.36
172	Tue 2023-06-20		58	87.11
173	Wed 2023-06-21		113	87.33
174	Thu 2023-06-22		76	87.23
175	Fri 2023-06-23		89	87.25
176	Sat 2023-06-24		50	87.25
177	Sun 2023-06-25		105	87.25
178	Mon 2023-06-26		91	87.28
179	Tue 2023-06-27		59	87.06
180	Wed 2023-06-28		77	86.98
181	Thu 2023-06-29		69	86.84
182	Fri 2023-06-30		99	86.93
183	Sat 2023-07-01		82	86.93
184	Sun 2023-07-02		87	86.93
185	Mon 2023-07-03		125	87.04
186	Tue 2023-07-04		106	87.17
187	Wed 2023-07-05		108	87.29
188	Thu 2023-07-06		72	87.42
189	Fri 2023-07-07		59	87.17
190	Sat 2023-07-08		107	87.17
191	Sun 2023-07-09		81	87.17
192	Mon 2023-07-10		84	87.26
193	Tue 2023-07-11		95	87.48
194	Wed 2023-07-12		112	87.94
195	Thu 2023-07-13		65	87.65
196	Fri 2023-07-14		98	87.85
197	Sat 2023-07-15		73	87.85
198	Sun 2023-07-16		100	87.85
199	Mon 2023-07-17		123	87.86
200	Tue 2023-07-18		116	88.36
Sheet1

Cell Formulas
Range	Formula
D2:D200	D2	=IFERROR(SUMPRODUCT(($A$2:A2>=EDATE($A2,-6))*($A$2:A2<=($A2))*($C$2:$C2)*(WEEKDAY($A$2:$A2,2)<6)) / SUMPRODUCT(($A$2:A2>=EDATE($A2,-6))*($A$2:A2<=($A2))*(WEEKDAY($A$2:$A2,2)<6)),"")

 

[Coyotex3]

If you have LET functions this can probably be done a little easier. but try this:
(you do not need a column for day of week, unless you want it in a separate column, this formula reads the weekday number from the date column)

Book1
	A	B	C	D
1	Date		Earnings	Last 6 Months
2	Sun 2023-01-01		67
3	Mon 2023-01-02		111	111.00
4	Tue 2023-01-03		89	100.00
5	Wed 2023-01-04		92	97.33
6	Thu 2023-01-05		55	86.75
7	Fri 2023-01-06		92	87.80
8	Sat 2023-01-07		104	87.80
9	Sun 2023-01-08		120	87.80
10	Mon 2023-01-09		72	85.17
11	Tue 2023-01-10		66	82.43
12	Wed 2023-01-11		53	78.75
13	Thu 2023-01-12		103	81.44
14	Fri 2023-01-13		72	80.50
15	Sat 2023-01-14		118	80.50
16	Sun 2023-01-15		114	80.50
17	Mon 2023-01-16		121	84.18
18	Tue 2023-01-17		51	81.42
19	Wed 2023-01-18		125	84.77
20	Thu 2023-01-19		82	84.57
21	Fri 2023-01-20		97	85.40
22	Sat 2023-01-21		108	85.40
23	Sun 2023-01-22		114	85.40
24	Mon 2023-01-23		115	87.25
25	Tue 2023-01-24		78	86.71
26	Wed 2023-01-25		77	86.17
27	Thu 2023-01-26		95	86.63
28	Fri 2023-01-27		102	87.40
29	Sat 2023-01-28		72	87.40
30	Sun 2023-01-29		114	87.40
31	Mon 2023-01-30		122	89.05
32	Tue 2023-01-31		57	87.59
33	Wed 2023-02-01		77	87.13
34	Thu 2023-02-02		105	87.88
35	Fri 2023-02-03		88	87.88
36	Sat 2023-02-04		95	87.88
37	Sun 2023-02-05		70	87.88
38	Mon 2023-02-06		89	87.92
39	Tue 2023-02-07		95	88.19
40	Wed 2023-02-08		110	88.96
41	Thu 2023-02-09		112	89.76
42	Fri 2023-02-10		87	89.67
43	Sat 2023-02-11		70	89.67
44	Sun 2023-02-12		63	89.67
45	Mon 2023-02-13		116	90.52
46	Tue 2023-02-14		74	90.00
47	Wed 2023-02-15		74	89.52
48	Thu 2023-02-16		110	90.12
49	Fri 2023-02-17		66	89.43
50	Sat 2023-02-18		93	89.43
51	Sun 2023-02-19		72	89.43
52	Mon 2023-02-20		60	88.61
53	Tue 2023-02-21		80	88.38
54	Wed 2023-02-22		101	88.71
55	Thu 2023-02-23		58	87.92
56	Fri 2023-02-24		79	87.70
57	Sat 2023-02-25		84	87.70
58	Sun 2023-02-26		55	87.70
59	Mon 2023-02-27		64	87.12
60	Tue 2023-02-28		103	87.50
61	Wed 2023-03-01		113	88.09
62	Thu 2023-03-02		122	88.86
63	Fri 2023-03-03		66	88.36
64	Sat 2023-03-04		112	88.36
65	Sun 2023-03-05		93	88.36
66	Mon 2023-03-06		108	88.78
67	Tue 2023-03-07		90	88.81
68	Wed 2023-03-08		62	88.25
69	Thu 2023-03-09		53	87.53
70	Fri 2023-03-10		62	87.02
71	Sat 2023-03-11		87	87.02
72	Sun 2023-03-12		63	87.02
73	Mon 2023-03-13		59	86.47
74	Tue 2023-03-14		102	86.77
75	Wed 2023-03-15		115	87.30
76	Thu 2023-03-16		109	87.70
77	Fri 2023-03-17		77	87.51
78	Sat 2023-03-18		80	87.51
79	Sun 2023-03-19		79	87.51
80	Mon 2023-03-20		69	87.18
81	Tue 2023-03-21		121	87.77
82	Wed 2023-03-22		91	87.83
83	Thu 2023-03-23		57	87.31
84	Fri 2023-03-24		75	87.10
85	Sat 2023-03-25		100	87.10
86	Sun 2023-03-26		117	87.10
87	Mon 2023-03-27		125	87.72
88	Tue 2023-03-28		101	87.94
89	Wed 2023-03-29		83	87.86
90	Thu 2023-03-30		71	87.59
91	Fri 2023-03-31		71	87.34
92	Sat 2023-04-01		123	87.34
93	Sun 2023-04-02		118	87.34
94	Mon 2023-04-03		60	86.92
95	Tue 2023-04-04		68	86.64
96	Wed 2023-04-05		104	86.90
97	Thu 2023-04-06		72	86.68
98	Fri 2023-04-07		70	86.44
99	Sat 2023-04-08		104	86.44
100	Sun 2023-04-09		76	86.44
101	Mon 2023-04-10		74	86.27
102	Tue 2023-04-11		97	86.42
103	Wed 2023-04-12		50	85.92
104	Thu 2023-04-13		52	85.46
105	Fri 2023-04-14		59	85.11
106	Sat 2023-04-15		106	85.11
107	Sun 2023-04-16		57	85.11
108	Mon 2023-04-17		53	84.68
109	Tue 2023-04-18		114	85.06
110	Wed 2023-04-19		57	84.71
111	Thu 2023-04-20		102	84.92
112	Fri 2023-04-21		61	84.63
113	Sat 2023-04-22		107	84.63
114	Sun 2023-04-23		66	84.63
115	Mon 2023-04-24		92	84.72
116	Tue 2023-04-25		101	84.91
117	Wed 2023-04-26		87	84.94
118	Thu 2023-04-27		58	84.62
119	Fri 2023-04-28		121	85.05
120	Sat 2023-04-29		67	85.05
121	Sun 2023-04-30		85	85.05
122	Mon 2023-05-01		121	85.47
123	Tue 2023-05-02		121	85.87
124	Wed 2023-05-03		100	86.03
125	Thu 2023-05-04		110	86.30
126	Fri 2023-05-05		115	86.62
127	Sat 2023-05-06		102	86.62
128	Sun 2023-05-07		53	86.62
129	Mon 2023-05-08		53	86.25
130	Tue 2023-05-09		55	85.91
131	Wed 2023-05-10		112	86.19
132	Thu 2023-05-11		118	86.53
133	Fri 2023-05-12		92	86.59
134	Sat 2023-05-13		101	86.59
135	Sun 2023-05-14		61	86.59
136	Mon 2023-05-15		89	86.61
137	Tue 2023-05-16		66	86.40
138	Wed 2023-05-17		76	86.30
139	Thu 2023-05-18		77	86.20
140	Fri 2023-05-19		74	86.08
141	Sat 2023-05-20		97	86.08
142	Sun 2023-05-21		105	86.08
143	Mon 2023-05-22		109	86.31
144	Tue 2023-05-23		78	86.23
145	Wed 2023-05-24		115	86.50
146	Thu 2023-05-25		70	86.35
147	Fri 2023-05-26		67	86.16
148	Sat 2023-05-27		55	86.16
149	Sun 2023-05-28		78	86.16
150	Mon 2023-05-29		87	86.17
151	Tue 2023-05-30		110	86.39
152	Wed 2023-05-31		123	86.73
153	Thu 2023-06-01		75	86.62
154	Fri 2023-06-02		83	86.59
155	Sat 2023-06-03		96	86.59
156	Sun 2023-06-04		77	86.59
157	Mon 2023-06-05		109	86.79
158	Tue 2023-06-06		112	87.02
159	Wed 2023-06-07		70	86.87
160	Thu 2023-06-08		83	86.83
161	Fri 2023-06-09		117	87.10
162	Sat 2023-06-10		124	87.10
163	Sun 2023-06-11		61	87.10
164	Mon 2023-06-12		63	86.89
165	Tue 2023-06-13		107	87.06
166	Wed 2023-06-14		63	86.86
167	Thu 2023-06-15		113	87.08
168	Fri 2023-06-16		83	87.04
169	Sat 2023-06-17		95	87.04
170	Sun 2023-06-18		108	87.04
171	Mon 2023-06-19		125	87.36
172	Tue 2023-06-20		58	87.11
173	Wed 2023-06-21		113	87.33
174	Thu 2023-06-22		76	87.23
175	Fri 2023-06-23		89	87.25
176	Sat 2023-06-24		50	87.25
177	Sun 2023-06-25		105	87.25
178	Mon 2023-06-26		91	87.28
179	Tue 2023-06-27		59	87.06
180	Wed 2023-06-28		77	86.98
181	Thu 2023-06-29		69	86.84
182	Fri 2023-06-30		99	86.93
183	Sat 2023-07-01		82	86.93
184	Sun 2023-07-02		87	86.93
185	Mon 2023-07-03		125	87.04
186	Tue 2023-07-04		106	87.17
187	Wed 2023-07-05		108	87.29
188	Thu 2023-07-06		72	87.42
189	Fri 2023-07-07		59	87.17
190	Sat 2023-07-08		107	87.17
191	Sun 2023-07-09		81	87.17
192	Mon 2023-07-10		84	87.26
193	Tue 2023-07-11		95	87.48
194	Wed 2023-07-12		112	87.94
195	Thu 2023-07-13		65	87.65
196	Fri 2023-07-14		98	87.85
197	Sat 2023-07-15		73	87.85
198	Sun 2023-07-16		100	87.85
199	Mon 2023-07-17		123	87.86
200	Tue 2023-07-18		116	88.36
Sheet1

Cell Formulas
Range	Formula
D2:D200	D2	=IFERROR(SUMPRODUCT(($A$2:A2>=EDATE($A2,-6))*($A$2:A2<=($A2))*($C$2:$C2)*(WEEKDAY($A$2:$A2,2)<6)) / SUMPRODUCT(($A$2:A2>=EDATE($A2,-6))*($A$2:A2<=($A2))*(WEEKDAY($A$2:$A2,2)<6)),"")

Don't know if this is what OP is asking for, but would using Average and Filter work to get the average?

Just saw that OP is using Excel 2019. Amended the previous answer.

PERSONAL.xlsm
	A	B	C	D	E
1	1/1/2023	1	359		313.2333
2	1/2/2023	2	303
3	1/3/2023	3	227
4	1/4/2023	4	470
5	1/5/2023	5	478
6	1/6/2023	6	321
7	1/7/2023	7	258
8	1/8/2023	1	344
9	1/9/2023	2	228
10	1/10/2023	3	147
11	1/11/2023	4	493
12	1/12/2023	5	365
13	1/13/2023	6	101
14	1/14/2023	7	480
15	1/15/2023	1	436
16	1/16/2023	2	85
17	1/17/2023	3	379
18	1/18/2023	4	365
19	1/19/2023	5	233
20	1/20/2023	6	381
21	1/21/2023	7	275
22	1/22/2023	1	384
23	1/23/2023	2	139
24	1/24/2023	3	178
25	1/25/2023	4	304
26	1/26/2023	5	456
27	1/27/2023	6	421
28	1/28/2023	7	284
29	1/29/2023	1	299
30	1/30/2023	2	378
31	1/31/2023	3	347
32	2/1/2023	4	404
33	2/2/2023	5	106
34	2/3/2023	6	157
35	2/4/2023	7	128
36	2/5/2023	1	336
37	2/6/2023	2	179
38	2/7/2023	3	313
39	2/8/2023	4	253
40	2/9/2023	5	409
41	2/10/2023	6	184
Sheet4

Cell Formulas
Range	Formula
E1	E1	=AVERAGEIFS(C1:C41,B1:B41,"<"&6)

 

[Carlos5]

Thank you,
I dso not have the LET Function . I will try this tomorrow.
Thanks again!

 

[Carlos5]

Don't know if this is what OP is asking for, but would using Average and Filter work to get the average?

Just saw that OP is using Excel 2019. Amended the previous answer.

PERSONAL.xlsm
	A	B	C	D	E
1	1/1/2023	1	359		313.2333
2	1/2/2023	2	303
3	1/3/2023	3	227
4	1/4/2023	4	470
5	1/5/2023	5	478
6	1/6/2023	6	321
7	1/7/2023	7	258
8	1/8/2023	1	344
9	1/9/2023	2	228
10	1/10/2023	3	147
11	1/11/2023	4	493
12	1/12/2023	5	365
13	1/13/2023	6	101
14	1/14/2023	7	480
15	1/15/2023	1	436
16	1/16/2023	2	85
17	1/17/2023	3	379
18	1/18/2023	4	365
19	1/19/2023	5	233
20	1/20/2023	6	381
21	1/21/2023	7	275
22	1/22/2023	1	384
23	1/23/2023	2	139
24	1/24/2023	3	178
25	1/25/2023	4	304
26	1/26/2023	5	456
27	1/27/2023	6	421
28	1/28/2023	7	284
29	1/29/2023	1	299
30	1/30/2023	2	378
31	1/31/2023	3	347
32	2/1/2023	4	404
33	2/2/2023	5	106
34	2/3/2023	6	157
35	2/4/2023	7	128
36	2/5/2023	1	336
37	2/6/2023	2	179
38	2/7/2023	3	313
39	2/8/2023	4	253
40	2/9/2023	5	409
41	2/10/2023	6	184
Sheet4

Cell Formulas
Range	Formula
E1	E1	=AVERAGEIFS(C1:C41,B1:B41,"<"&6)

Hi,
How would this exclude weekends?
Thanks

 

[Coyotex3]

Hi,
How would this exclude weekends?
Thanks

Oops. That formula did not exclude the weekends properly.

AVERAGEIFS(C1:C41,B1:B41,"<"&7,B1:B41,">"&1)

This one does. Excel assigns 1 to Sunday and 7 to Saturday. This formula looks for the weekdays in column B which are greater than one and less than 6. I'm unsure if this includes the rolling average as you requested though.

 

[Carlos5]

Hello again,
While it looks to me like this will work, I realize that it isn't what I was looking for. I'm sorry if I wasn't exact enough. Rather than a running total in a new column, I am looking for a single cell that gives me the average for the last 6 months excluding weekends. Does that make more sense?
Thanks

 

[Coyotex3]

Hello again,
While it looks to me like this will work, I realize that it isn't what I was looking for. I'm sorry if I wasn't exact enough. Rather than a running total in a new column, I am looking for a single cell that gives me the average for the last 6 months excluding weekends. Does that make more sense?
Thanks

In that case, then maybe this formula works afterall?

PERSONAL.xlsm
	A	B	C	D	E
1	1/1/2023	1	359		293.4667
2	1/2/2023	2	303
3	1/3/2023	3	227
4	1/4/2023	4	470
5	1/5/2023	5	478
6	1/6/2023	6	321
7	1/7/2023	7	258
8	1/8/2023	1	344
9	1/9/2023	2	228
10	1/10/2023	3	147
11	1/11/2023	4	493
12	1/12/2023	5	365
13	1/13/2023	6	101
14	1/14/2023	7	480
15	1/15/2023	1	436
16	1/16/2023	2	85
17	1/17/2023	3	379
18	1/18/2023	4	365
19	1/19/2023	5	233
20	1/20/2023	6	381
21	1/21/2023	7	275
22	1/22/2023	1	384
23	1/23/2023	2	139
24	1/24/2023	3	178
25	1/25/2023	4	304
26	1/26/2023	5	456
27	1/27/2023	6	421
28	1/28/2023	7	284
29	1/29/2023	1	299
30	1/30/2023	2	378
31	1/31/2023	3	347
32	2/1/2023	4	404
33	2/2/2023	5	106
34	2/3/2023	6	157
35	2/4/2023	7	128
36	2/5/2023	1	336
37	2/6/2023	2	179
38	2/7/2023	3	313
39	2/8/2023	4	253
40	2/9/2023	5	409
41	2/10/2023	6	184
Sheet4

Cell Formulas
Range	Formula
E1	E1	=AVERAGEIFS(C1:C41,B1:B41,"<"&7,B1:B41,">"&1)

 

[Carlos5]

Oops. That formula did not exclude the weekends properly.

AVERAGEIFS(C1:C41,B1:B41,"<"&7,B1:B41,">"&1)

This one does. Excel assigns 1 to Sunday and 7 to Saturday. This formula looks for the weekdays in column B which are greater than one and less than 6. I'm unsure if this includes the rolling average as you requested though.

Thanks, It gives me something to work with.

 

[Coyotex3]

Thanks, It gives me something to work with. I am trying to avoid VBA but if I must

Formula above should work for what you need(Post # 8). It however will not automatically filter for the last 6 months. Another alternative would be to use a pivot table? You can create a pivot table and filter out the days you don't want. Then you can summarize the values as an average to get your numbers.