Average 3 months score before and after

[datastudent]

Hi,

I'm trying to get the 3 months average score of before and after training. I got the after training but I'm having trouble with the before training.

Column AC highlighted in yellow shows the expected results. The 3 months average score of before TR should be calculated from the training date plus 2 months prior.

Incumbent Dashboard 2023 (version 3).xlsx
	A																					V	W	X					AC	AD			AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
2	Concat																					TR Date	TR Year	TR Month					BeforeTRScore22	AfterTRScore22			Jan22'	Feb22'	Mar22'	Apr22'	May22'	Jun22'	Jul22'	Aug22'	Sep22'	Oct22'	Nov22'	Dec22'
3	asdf																					8/23/2022	2022	Aug22'					9.37	9.96								8.9	9.4	9.9	10.0	10.0	10.0	8.0
4	ghjk																					7/12/2022	2022	Jul22'					2.86	1.98			5.2	2.8	4.0	3.6	2.9			0.4	2.0	3.6	4.5	4.7
5	lzxc																					11/8/2022	2022	Nov22'					9.65	8.00								7.9	8.6	9.2	9.3	9.7	9.9	8.0
Sheet1

Cell Formulas
Range	Formula
AD3:AD5	AD3	=IFERROR(AVERAGE(OFFSET($A3,0,MATCH($X3,$2:$2,0)):OFFSET($A3,0,MATCH($X3,$2:$2,0)+2)),"")

 

[ExceLoki]

try this

Book1
	A																					V	W	X						AD	AE			AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS
1	Concat																					TR Date	TR Year	TR Month						BeforeTR Score22	AfterTR Score22			Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22
2	asdf																					8/23/2022	2022	Aug-22						9.37	9.96								8.9	9.35	9.87	9.95	9.97	9.96	8
3	ghjk																					7/12/2022	2022	Jul-22						2.86	1.98			5.15	2.82	3.97	3.64	2.86			0.35	2	3.6	4.53	4.66
4	lzxc																					11/8/2022	2022	Nov-22						9.65	8.00								7.88	8.6	9.18	9.33	9.69	9.92	8
Sheet1

Cell Formulas
Range	Formula
AD2:AD4	AD2	=AVERAGE(IF(INDEX($AH2:$AX2,MATCH(DATE(YEAR($X2),MONTH($X2),1),$AH$1:$AX$1,0))<0,"",INDEX($AH2:$AX2,MATCH(DATE(YEAR($X2),MONTH($X2),1),$AH$1:$AX$1,0))),IF(INDEX($AH2:$AX2,MATCH(DATE(YEAR($X2),MONTH($X2),1),$AH$1:$AX$1,0)-1)<0,"",INDEX($AH2:$AX2,MATCH(DATE(YEAR($X2),MONTH($X2),1),$AH$1:$AX$1,0)-1)),IF(INDEX($AH2:$AX2,MATCH(DATE(YEAR($X2),MONTH($X2),1),$AH$1:$AX$1,0)-2)<0,"",INDEX($AH2:$AX2,MATCH(DATE(YEAR($X2),MONTH($X2),1),$AH$1:$AX$1,0)-2)))
AE2:AE4	AE2	=AVERAGE(IF(INDEX($AH2:$AX2,MATCH(DATE(YEAR($X2),MONTH($X2),1),$AH$1:$AX$1,0)+1)<0,"",INDEX($AH2:$AX2,MATCH(DATE(YEAR($X2),MONTH($X2),1),$AH$1:$AX$1,0)+1)),IF(INDEX($AH2:$AX2,MATCH(DATE(YEAR($X2),MONTH($X2),1),$AH$1:$AX$1,0)+2)<0,"",INDEX($AH2:$AX2,MATCH(DATE(YEAR($X2),MONTH($X2),1),$AH$1:$AX$1,0)+2)),IF(INDEX($AH2:$AX2,MATCH(DATE(YEAR($X2),MONTH($X2),1),$AH$1:$AX$1,0)+3)<0,"",INDEX($AH2:$AX2,MATCH(DATE(YEAR($X2),MONTH($X2),1),$AH$1:$AX$1,0)+3)))

 

[Eric W]

Some shorter options:

Book2
	A																					V	W	X					AC	AD			AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
2	Concat																					TR Date	TR Year	TR Month					BeforeTRScore22	AfterTRScore22			Jan22'	Feb22'	Mar22'	Apr22'	May22'	Jun22'	Jul22'	Aug22'	Sep22'	Oct22'	Nov22'	Dec22'
3	asdf																					8/23/2022	2022	Aug22'					9.37	9.96								8.9	9.35	9.87	9.95	9.97	9.96	8
4	ghjk																					7/12/2022	2022	Jul22'					2.86	1.98			5.15	2.82	3.97	3.64	2.86			0.35	2	3.6	4.53	4.66
5	lzxc																					11/8/2022	2022	Nov22'					9.65	8.00								7.88	8.6	9.18	9.33	9.69	9.92	8
Sheet1

Cell Formulas
Range	Formula
AC3:AC5	AC3	=AVERAGE(OFFSET(AE3,0,MATCH(X3,AE$2:AR$2,0)-3,,3))
AD3:AD5	AD3	=AVERAGE(OFFSET(AE3,0,MATCH(X3,AE$2:AR$2,0),,3))

 

[datastudent]

Thanks guys! I really appreciate the help