Average cells based on a date criteria, with blanks cells in various rows...

[mad3]

I want to average the cells in third column (c) based on the dates in first column (A) being less than the date in the top left cell (A1). However, since there is a blank cell within the range, I am getting a divide by zero error. How do I get around this?


column A column B column C

3/23/2024​
	Boston
Date	Team	PCT
2/12/24	New York (A)	.381
2/17/24	Toronto	.556
1 Day Off
2/21/24	Baltimore	.619
3/3/24	San Francisco	.333
3/14/24	Cleveland	.286
1 Day Off
3/23/24	Texas	.667
3/24/24	Arizona	.476
3/31/24	Kansas City	.429
4/7/24	New York (N)	.571

 

[Cubist]

How about this?

Book4
	A	B	C	D	E
1	3/23/24
2		Boston
3	Date	Team	PCT		Result
4	2/12/24	New York (A)	0.381		0.435
5	2/17/24	Toronto	0.556
6	1 Day Off
7	2/21/24	Baltimore	0.619
8	3/3/24	San Francisco	0.333
9	3/14/24	Cleveland	0.286
10	1 Day Off
11	3/23/24	Texas	0.667
12	3/24/24	Arizona	0.476
13	3/31/24	Kansas City	0.429
14	4/7/24	New York (N)	0.571
Sheet1

Cell Formulas
Range	Formula
E4	E4	=AVERAGEIFS(C4:C14, A4:A14, "<" & A1, C4:C14, "<>")

 

[Peter_SSs]

However, since there is a blank cell within the range, I am getting a divide by zero error. How do I get around this?

What formula were you using?
Do you actually need the extra condition in the AVERAGEIFS formula? Do you have circumstances where the shorter formula below would not work?
If so, could we have some sample data with XL2BB?

24 03 24.xlsm
	A	B	C	D	E	F
1	23/03/2024
2		Boston
3	Date	Team	PCT		Result
4	12/02/2024	New York (A)	0.381		0.47225	0.47225
5	17/02/2024	Toronto	0.556
6	1 Day Off
7	21/02/2024	Baltimore	0.619
8	3/03/2024	San Francisco	0.333
9	3/14/24	Cleveland	0.286
10	1 Day Off
11	23/03/2024	Texas	0.667
12	24/03/2024	Arizona	0.476
13	31/03/2024	Kansas City	0.429
14	4/07/2024	New York (N)	0.571
Averageifs

Cell Formulas
Range	Formula
E4	E4	=AVERAGEIFS(C4:C14, A4:A14, "<" & A1, C4:C14, "<>")
F4	F4	=AVERAGEIFS(C4:C14,A4:A14,"<"&A1)

 

[mad3]

How about this?

Book4
	A	B	C	D	E
1	3/23/24
2		Boston
3	Date	Team	PCT		Result
4	2/12/24	New York (A)	0.381		0.435
5	2/17/24	Toronto	0.556
6	1 Day Off
7	2/21/24	Baltimore	0.619
8	3/3/24	San Francisco	0.333
9	3/14/24	Cleveland	0.286
10	1 Day Off
11	3/23/24	Texas	0.667
12	3/24/24	Arizona	0.476
13	3/31/24	Kansas City	0.429
14	4/7/24	New York (N)	0.571
Sheet1

Cell Formulas
Range	Formula
E4	E4	=AVERAGEIFS(C4:C14, A4:A14, "<" & A1, C4:C14, "<>")

What formula were you using?
Do you actually need the extra condition in the AVERAGEIFS formula? Do you have circumstances where the shorter formula below would not work?
If so, could we have some sample data with XL2BB?

24 03 24.xlsm
	A	B	C	D	E	F
1	23/03/2024
2		Boston
3	Date	Team	PCT		Result
4	12/02/2024	New York (A)	0.381		0.47225	0.47225
5	17/02/2024	Toronto	0.556
6	1 Day Off
7	21/02/2024	Baltimore	0.619
8	3/03/2024	San Francisco	0.333
9	3/14/24	Cleveland	0.286
10	1 Day Off
11	23/03/2024	Texas	0.667
12	24/03/2024	Arizona	0.476
13	31/03/2024	Kansas City	0.429
14	4/07/2024	New York (N)	0.571
Averageifs

Cell Formulas
Range	Formula
E4	E4	=AVERAGEIFS(C4:C14, A4:A14, "<" & A1, C4:C14, "<>")
F4	F4	=AVERAGEIFS(C4:C14,A4:A14,"<"&A1)

Strength of Schedule SNBL.xlsx
	A	B	C	D
34		Boston
35	Date	Team	PCT	SOS
36	2/11/24	New York (A)	.381	.596
37	2/18/24	Toronto	.524	.482
38		1 Day Off
39	2/25/24	Baltimore	.619	.524
40	3/3/24	San Francisco	.333	.545
41	3/10/24	Cleveland	.286	.457
42		1 Day Off
43	3/17/24	Texas	.619	.375
44	3/24/24	Arizona	.476	.553
45	3/31/24	Kansas City	.429	.562
46	4/7/24	New York (N)	.571	.420
Standings_SOS

Cell Formulas
Range	Formula
C36:C37,C43:C46,C39:C41	C36	=VLOOKUP(B36,$A$2:$D$32,4,FALSE)
D36:D37,D43:D46,D39:D41	D36	=VLOOKUP(B36,$A$2:$M$32,13,FALSE)

 

[mad3]

Strength of Schedule SNBL.xlsx
	A	B	C	D
34		Boston
35	Date	Team	PCT	SOS
36	2/11/24	New York (A)	.381	.596
37	2/18/24	Toronto	.524	.482
38		1 Day Off
39	2/25/24	Baltimore	.619	.524
40	3/3/24	San Francisco	.333	.545
41	3/10/24	Cleveland	.286	.457
42		1 Day Off
43	3/17/24	Texas	.619	.375
44	3/24/24	Arizona	.476	.553
45	3/31/24	Kansas City	.429	.562
46	4/7/24	New York (N)	.571	.420
Standings_SOS

Cell Formulas
Range	Formula
C36:C37,C43:C46,C39:C41	C36	=VLOOKUP(B36,$A$2:$D$32,4,FALSE)
D36:D37,D43:D46,D39:D41	D36	=VLOOKUP(B36,$A$2:$M$32,13,FALSE)

Strength of Schedule SNBL.xlsx
	A	B	C	D
63	7/7/24	Oakland	.524	.472
64	7/14/24	St. Louis	.571	.531
65	7/21/24	Kansas City	.429	.562
66	7/28/24	Seattle	.333	.575
67		1 Day Off
68	8/4/24	Miami	.500	.546
69	8/11/24	Milwaukee	.684	.401
70	8/18/24	Minnesota	.524	.403
71	8/25/24	Los Angeles (A)	.524	.571
72		1 Day Off
73	9/1/24	Cincinnati	.455	.539
74	9/8/24	Detroit	.571	.500
75	9/15/24	New York (A)	.381	.596
76		1 Day Off
77	9/22/24	Toronto	.524	.482
78	9/29/24	Baltimore	.619	.524
79		Strength of schedule played>	.460	.496
Standings_SOS

Cell Formulas
Range	Formula
C63:C66,C77:C78,C73:C75,C68:C71	C63	=VLOOKUP(B63,$A$2:$D$32,4,FALSE)
D63:D66,D77:D78,D73:D75,D68:D71	D63	=VLOOKUP(B63,$A$2:$M$32,13,FALSE)
C79	C79	=AVERAGEIFS(C$36:C$78, A$36:A$78, "<" & $A$33, C$36:C$78, "<>")
D79	D79	=AVERAGEIFS(D$36:D$78, A$36:A$78, "<" & $A$33, D$36:D$78, "<>")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B62:B78,E62:E78,H62:H78,K62:K78,N62:N78,Q62:Q78,B108:B124,E108:E124,H108:H124,K108:K124,N108:N124,Q108:Q124,B154:B170,E154:E170,H154:H170,K154:K170,N154:N170,Q154:Q170	Expression	=D62>=0.5	text	NO

 

[Peter_SSs]

Thanks for getting XL2BB going.

Your layout is different to the layout shown in post 1.

Also, your XL2BB sample does not show all the information that the formulas refer to. Can you make up a much smaller example that shows the issue and post that with XL2BB?