Averageifs returns #div/0 using a date range

[jsical]

I am trying to find the avg of a value base on a range of dates. In column A i have the dates (there are empty cells in this column", column C I have the value i want to AVG. i am using the following formula =AVERAGEIFS(C:C, A:A, "<= 1/1/27", A:A, ">= 1/1/2021"), but continued to get a #/div/0. There a plenty of line items with date in between the criteria, I am not sure if the blank cells are causing the issue or not. I have also try doing the following =AVERAGEIFS(C:C, A:A, ">= 1/1/21", A:A, "<= 1/1/2027") and i get the same error. can you help me? thanks

 

[kevin9999]

I'm having problems replicating the error you're getting. Could you provide a sample of your actual data using the XL2BB Tool?

hyperlink.xlsb
	A	B	C	D	E	F	G	H	I
1	Date		Value		Start	End
2	1/01/2020		100		1/01/2021	1/01/2027
3	1/01/2021		100
4
5	1/01/2023		50		AVERAGEIFS:	93.75
6
7	1/01/2025		75		AVERAGEIFS:	93.75
8	1/01/2026
9	1/01/2027		150
10	1/01/2028
11	1/01/2029		100
12
13
Sheet3

Cell Formulas
Range	Formula
F5	F5	=AVERAGEIFS(C:C,A:A,">="&E2,A:A,"<="&F2)
F7	F7	=AVERAGEIFS(C:C,A:A,">=1/01/2021",A:A,"<=1/01/2027")

 

[jsical]

I'm having problems replicating the error you're getting. Could you provide a sample of your actual data using the XL2BB Tool?

hyperlink.xlsb
	A	B	C	D	E	F	G	H	I
1	Date		Value		Start	End
2	1/01/2020		100		1/01/2021	1/01/2027
3	1/01/2021		100
4
5	1/01/2023		50		AVERAGEIFS:	93.75
6
7	1/01/2025		75		AVERAGEIFS:	93.75
8	1/01/2026
9	1/01/2027		150
10	1/01/2028
11	1/01/2029		100
12
13
Sheet3

Cell Formulas
Range	Formula
F5	F5	=AVERAGEIFS(C:C,A:A,">="&E2,A:A,"<="&F2)
F7	F7	=AVERAGEIFS(C:C,A:A,">=1/01/2021",A:A,"<=1/01/2027")

Thank you for your assistance. I am baffled by the issue but I was able to solve it. I copied and paste the data into Notepad, then copy back to excel; it work perfectly. I am guessing there was a glitch with the format, because I made sure the dates were format properly.

 

[kevin9999]

Thank you for your assistance. I am baffled by the issue but I was able to solve it. I copied and paste the data into Notepad, then copy back to excel; it work perfectly. I am guessing there was a glitch with the format, because I made sure the dates were format properly.

Glad you fixed the problem