Average of 3 months score from a given month

[datastudent]

Hi,
I want to get the 3 months average score from the promotion month.
I need a formula that will show the expected result in column G.

B2 is the promotion Month so from that month I want to get the average score which should be Q2:S2.
For promotion month B4, average score from N4:P4. Hope somebody can help me

Incumbent Dashboard 2023.xlsx
	A	B	C	D			G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Promotion Date	Promotion Month	3 months from Promotion	3 months from Promotion2			3 months score	Jan22'	Feb22'	Mar22'	Apr22'	May22'	Jun22'	Jul22'	Aug22'	Sep22'	Oct22'	Nov22'	Dec22'
2	9/12/2022	Sep22'	12/11/2022	Dec22'			0.8	1.1	0.6	1.1	0.6			0.6	1.1	0.6	0.6	1.1	0.6
3	-	-	-	-					0.3	1.0
4	6/12/2022	Jun22'	9/10/2022	Sep22'			5.3	6.0	5.0	5.0	5.0	6.0	5.0	6.0	5.0	5.0	5.0	6.0	5.0
5	8/22/2022	Aug22'	11/20/2022	Nov22'			6.0	5.9	3.4	6.0	6.0	6.0	5.8	6.5	6.0	6.0	6.0	6.0	6.0
6	8/6/2021	Aug21'	11/4/2021	Nov21'							5.8	6.5	6.0	6.0	6.0	6.0	6.0
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=TEXT([@[Promotion Date]],"mmmyy'")
C2:C6	C2	=IFERROR(A2+90,"-")
D2:D6	D2	=TEXT(C2,"mmmyy'")

 

[James006]

Hi,
For cell G2, you can test

=AVERAGE(OFFSET(A2,0,MATCH(B2,$1:$1,0)):OFFSET(A2,0,MATCH(B2,$1:$1,0)+2))

 

[datastudent]

Hi,
For cell G2, you can test

=AVERAGE(OFFSET(A2,0,MATCH(B2,$1:$1,0)):OFFSET(A2,0,MATCH(B2,$1:$1,0)+2))

Ahm hey @James006 , thanks for answering. I tried the formula and for some reason I'm getting a #DIV/0!. Do you mind checking it? maybe I'm doing something wrong?

This is my actual file so the columns are a little different. So we are looking at the answer for column AB which is the 3 month average score from promotion date.

Incumbent Dashboard 2023.xlsx
	P		R	S	T								AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP
2	Promotion Date		Promotion Month	90 days	3rd Month								3 Month Score	BeforeTRScore22	AfterTRScore22	Jan22'	Feb22'	Mar22'	Apr22'	May22'	Jun22'	Jul22'	Aug22'	Sep22'	Oct22'	Nov22'	Dec22'
14	6/12/2022		Jun22'	9/10/2022	Sep22'								#DIV/0!		3.64							3.6
29	1/31/2022		Jan22'	5/1/2022	May22'								#DIV/0!	1.83			5.0	2.0	0.4	1.2	2.5	2.4	1.6	1.1		0.2
36	4/25/2022		Apr22'	7/24/2022	Jul22'								#DIV/0!		6.51						8.3			6.6	6.7	7.1	3.8
Active TL List

Cell Formulas
Range	Formula
AB14,AB36,AB29	AB14	=AVERAGE(OFFSET(P14,0,MATCH(R14,$2:$2,0)):OFFSET(P14,0,MATCH(R14,$2:$2,0)+2))
AC14,AC36,AC29	AC14	=IFERROR(AVERAGE(IF(ISNUMBER(MATCH($X14,AE$2:AP$2,0)),AE14:INDEX(AE14:AP14,MATCH($X14,AE$2:AP$2,0)),IF(MID($X14&99999,4,2)-MID(AE$2,4,2)>=0,AE14:AP14,""))),"")
AD14,AD36,AD29	AD14	=IFERROR(AVERAGE(IF(ISNUMBER(MATCH($X14,AE$2:AP$2,0)),INDEX(AE14:AP14,MATCH($X14,AE$2:AP$2,0)+1):AP14,IF(MID($X14&99999,4,2)-MID(AE$2,4,2)>=0,"",AE14:AP14))),"")

 

[James006]

Hi again,
For cell AB14, you can test:

=AVERAGE(OFFSET(A14,0,MATCH(R14,$2:$2,0)):OFFSET(A14,0,MATCH(R14,$2:$2,0)+2))

 

[datastudent]

Hi again,
For cell AB14, you can test:

=AVERAGE(OFFSET(A14,0,MATCH(R14,$2:$2,0)):OFFSET(A14,0,MATCH(R14,$2:$2,0)+2))

Oh wow, thank you so much! it works.

 

[James006]

You are welcome