nesting within AVERAGEIF

[armsafna]

I'm trying to take an average of the "Average" column depending on what the month in the "Date" column is.

Date	Trial 1	Trial 2	Trial 3	Average
1/12/2023	100	101	102	=mean(B2:D2) = 101
1/13/2023	103	104	105	=mean(B3:D3) = 103
2/12/2023	106	107	108	=mean(B4:D4) = 105

I'm trying =AVERAGEIF(Table1[Average],MONTH(Table1[Date]=1)) and it's giving me DIV/0 error.
Any ideas on how to fix it or a better approach?

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Date	Trial 1	Trial 2	Trial 3	Average
2	12/01/2023	100	101	102	101		01/01/2023	102.5
3	13/01/2023	103	104	105	104
4	12/02/2023	106	107	108	107
5
View

Cell Formulas
Range	Formula
H2	H2	=AVERAGEIFS(E:E,A:A,">="&G2,A:A,"<"&EDATE(G2,1))
E2:E4	E2	=MEDIAN(B2:D2)

 

[armsafna]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Date	Trial 1	Trial 2	Trial 3	Average
2	12/01/2023	100	101	102	101		01/01/2023	102.5
3	13/01/2023	103	104	105	104
4	12/02/2023	106	107	108	107
5
View

Cell Formulas
Range	Formula
H2	H2	=AVERAGEIFS(E:E,A:A,">="&G2,A:A,"<"&EDATE(G2,1))
E2:E4	E2	=MEDIAN(B2:D2)

lmao you should just do my job for me. Thanks so much. If you don't mind my asking - why is "&" needed between ">=" and the date?

 

[Fluff]

It's to concatenate the date with the < or > sign.