Average from a table

[nikolacm]

I have a table which indicates indexation rates for each month and per year. Each column presents the year ending whereas each row indicates the monthly value per annum. In this case I would like to calculate the average rate of values indicated 12 months preceding June 2023. Manually I would use the formula AVERAGE by taking into account 5 months from column Dec-23 (January to June) and 6 months from column Dec-22 (July to December). How may I automate it ?

Thank you in advance,

Year Ending

Dec-22​

Dec-23​

Dec-24​

Dec-25​

Dec-26​

Dec-27​

Dec-28​

Dec-29​

Dec-30​

Dec-31​

Dec-32​

Dec-33​

Jan​

10,85%​

5,07%​

1,95%​

2,14%​

2,71%​

2,94%​

2,96%​

2,96%​

2,96%​

2,96%​

2,96%​

2,96%​

Feb​

10,85%​

5,07%​

1,95%​

2,14%​

2,71%​

2,94%​

2,96%​

2,96%​

2,96%​

2,96%​

2,96%​

2,96%​

Mar​

10,85%​

5,07%​

1,95%​

2,14%​

2,71%​

2,94%​

2,96%​

2,96%​

2,96%​

2,96%​

2,96%​

2,96%​

Apr​

10,85%​

5,07%​

1,95%​

2,14%​

2,71%​

2,94%​

2,96%​

2,96%​

2,96%​

2,96%​

2,96%​

2,96%​

May​

10,85%​

5,07%​

1,95%​

2,14%​

2,71%​

2,94%​

2,96%​

2,96%​

2,96%​

2,96%​

2,96%​

2,96%​

Jun​

10,85%​

5,07%​

1,95%​

2,14%​

2,71%​

2,94%​

2,96%​

2,96%​

2,96%​

2,96%​

2,96%​

2,96%​

Jul​

10,85%​

5,07%​

1,95%​

2,14%​

2,71%​

2,94%​

2,96%​

2,96%​

2,96%​

2,96%​

2,96%​

2,96%​

Aug​

10,85%​

5,07%​

1,95%​

2,14%​

2,71%​

2,94%​

2,96%​

2,96%​

2,96%​

2,96%​

2,96%​

2,96%​

Sep​

10,85%​

5,07%​

1,95%​

2,14%​

2,71%​

2,94%​

2,96%​

2,96%​

2,96%​

2,96%​

2,96%​

2,96%​

Oct​

10,85%​

5,07%​

1,95%​

2,14%​

2,71%​

2,94%​

2,96%​

2,96%​

2,96%​

2,96%​

2,96%​

2,96%​

Nov​

10,85%​

5,07%​

1,95%​

2,14%​

2,71%​

2,94%​

2,96%​

2,96%​

2,96%​

2,96%​

2,96%​

2,96%​

Dec​

10,85%​

5,07%​

1,95%​

2,14%​

2,71%​

2,94%​

2,96%​

2,96%​

2,96%​

2,96%​

2,96%​

2,96%​

 

[Georgiboy]

You mean like the below?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Year Ending	Dec-22	Dec-23	Dec-24	Dec-25	Dec-26	Dec-27	Dec-28	Dec-29	Dec-30	Dec-31	Dec-32	Dec-33
2	Jan	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
3	Feb	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
4	Mar	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
5	Apr	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
6	May	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
7	Jun	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
8	Jul	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
9	Aug	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
10	Sep	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
11	Oct	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
12	Nov	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
13	Dec	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
14			7.96%	3.51%	2.05%	2.43%	2.83%	2.95%	2.96%	2.96%	2.96%	2.96%	2.96%
Sheet1

Cell Formulas
Range	Formula
C14:M14	C14	=AVERAGE(B8:B13,C2:C7)

 

[Georgiboy]

I was also playing with the below although i may have overcomplicated it somewhat:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Year Ending	Dec-22	Dec-23	Dec-24	Dec-25	Dec-26	Dec-27	Dec-28	Dec-29	Dec-30	Dec-31	Dec-32	Dec-33
2	Jan	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
3	Feb	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
4	Mar	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
5	Apr	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
6	May	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
7	Jun	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
8	Jul	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
9	Aug	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
10	Sep	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
11	Oct	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
12	Nov	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
13	Dec	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
14			7.96%	3.51%	2.05%	2.43%	2.83%	2.95%	2.96%	2.96%	2.96%	2.96%	2.96%
Sheet1

Cell Formulas
Range	Formula
C14:M14	C14	=DROP(BYCOL(VSTACK(OFFSET(B2:M13,6,-1,6),OFFSET(B2:M13,1,,6)),LAMBDA(x,AVERAGE(x))),,1)
Dynamic array formulas.

 

[nikolacm]

You mean like the below?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Year Ending	Dec-22	Dec-23	Dec-24	Dec-25	Dec-26	Dec-27	Dec-28	Dec-29	Dec-30	Dec-31	Dec-32	Dec-33
2	Jan	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
3	Feb	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
4	Mar	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
5	Apr	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
6	May	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
7	Jun	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
8	Jul	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
9	Aug	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
10	Sep	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
11	Oct	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
12	Nov	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
13	Dec	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
14			7.96%	3.51%	2.05%	2.43%	2.83%	2.95%	2.96%	2.96%	2.96%	2.96%	2.96%
Sheet1

Cell Formulas
Range	Formula
C14:M14	C14	=AVERAGE(B8:B13,C2:C7)

Unfortunately no. Until now I was doing this each time. I am looking for a more automated way. More specifically, if you have the reference year and month on separate cells, which will be the average rate preceding 12 months before the reference date ? I gived a try by using AVERAGEIF and OFFSET but without any luck.

 

[Georgiboy]

I see, maybe more like the below then:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Year Ending	Dec-22	Dec-23	Dec-24	Dec-25	Dec-26	Dec-27	Dec-28	Dec-29	Dec-30	Dec-31	Dec-32	Dec-33
2	Jan	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%		Year	Dec-26
3	Feb	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%		Month	Mar
4	Mar	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
5	Apr	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%		Result	2.28%
6	May	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
7	Jun	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
8	Jul	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
9	Aug	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
10	Sep	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
11	Oct	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
12	Nov	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
13	Dec	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
Sheet1

Cell Formulas
Range	Formula
P5	P5	=LET(rng,B2:M13,dts,B1:M1,mnth,A2:A13,AVERAGE(VSTACK(OFFSET(OFFSET(rng,,MATCH(P2,dts,0)-1,,1),1,,MATCH(P3,mnth,0)),OFFSET(OFFSET(rng,,MATCH(P2,dts,0)-2,,1),MATCH(P3,mnth,0),,12-MATCH(P3,mnth,0)))))

Maybe someone can come up with a simpler version - i am still getting my head around the new functions

 

[nikolacm]

I see, maybe more like the below then:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Year Ending	Dec-22	Dec-23	Dec-24	Dec-25	Dec-26	Dec-27	Dec-28	Dec-29	Dec-30	Dec-31	Dec-32	Dec-33
2	Jan	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%		Year	Dec-26
3	Feb	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%		Month	Mar
4	Mar	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
5	Apr	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%		Result	2.28%
6	May	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
7	Jun	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
8	Jul	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
9	Aug	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
10	Sep	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
11	Oct	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
12	Nov	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
13	Dec	10.85%	5.07%	1.95%	2.14%	2.71%	2.94%	2.96%	2.96%	2.96%	2.96%	2.96%	2.96%
Sheet1

Cell Formulas
Range	Formula
P5	P5	=LET(rng,B2:M13,dts,B1:M1,mnth,A2:A13,AVERAGE(VSTACK(OFFSET(OFFSET(rng,,MATCH(P2,dts,0)-1,,1),1,,MATCH(P3,mnth,0)),OFFSET(OFFSET(rng,,MATCH(P2,dts,0)-2,,1),MATCH(P3,mnth,0),,12-MATCH(P3,mnth,0)))))

Maybe someone can come up with a simpler version - i am still getting my head around the new functions

It seems to be a hint for the sollution, however the calculated average seems to be wrong.

Thank you for your help!

 

[Georgiboy]

I have had a look and it looks ok to me, maybe you could add a little more detail other than the word 'wrong' as it does not give me much detail to look into.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Year Ending	01/12/2022	01/12/2023	01/12/2024	01/12/2025	01/12/2026	01/12/2027	01/12/2028	01/12/2029	01/12/2030	01/12/2031	01/12/2032	01/12/2033
2	Jan	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296		Year	01/12/2026
3	Feb	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296		Month	Mar
4	Mar	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
5	Apr	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296		Result	0.022825
6	May	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
7	Jun	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296		Manual formula	0.022825
8	Jul	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
9	Aug	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
10	Sep	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
11	Oct	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
12	Nov	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
13	Dec	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
14
15
16	Year Ending	01/12/2022	01/12/2023	01/12/2024	01/12/2025	01/12/2026	01/12/2027	01/12/2028	01/12/2029	01/12/2030	01/12/2031	01/12/2032	01/12/2033
17	Jan	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296		Year	01/12/2024
18	Feb	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296		Month	May
19	Mar	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
20	Apr	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296		Result	0.0377
21	May	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
22	Jun	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296		Manual formula	0.0377
23	Jul	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
24	Aug	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
25	Sep	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
26	Oct	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
27	Nov	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
28	Dec	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
Sheet1

Cell Formulas
Range	Formula
P5,P20	P5	=LET(rng,B2:M13,dts,B1:M1,mnth,A2:A13,AVERAGE(VSTACK(OFFSET(OFFSET(rng,,MATCH(P2,dts,0)-1,,1),1,,MATCH(P3,mnth,0)),OFFSET(OFFSET(rng,,MATCH(P2,dts,0)-2,,1),MATCH(P3,mnth,0),,12-MATCH(P3,mnth,0)))))
P7	P7	=AVERAGE(E5:E13,F2:F4)
P22	P22	=AVERAGE(C22:C28,D17:D21)

 

[nikolacm]

I have had a look and it looks ok to me, maybe you could add a little more detail other than the word 'wrong' as it does not give me much detail to look into.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Year Ending	01/12/2022	01/12/2023	01/12/2024	01/12/2025	01/12/2026	01/12/2027	01/12/2028	01/12/2029	01/12/2030	01/12/2031	01/12/2032	01/12/2033
2	Jan	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296		Year	01/12/2026
3	Feb	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296		Month	Mar
4	Mar	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
5	Apr	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296		Result	0.022825
6	May	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
7	Jun	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296		Manual formula	0.022825
8	Jul	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
9	Aug	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
10	Sep	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
11	Oct	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
12	Nov	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
13	Dec	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
14
15
16	Year Ending	01/12/2022	01/12/2023	01/12/2024	01/12/2025	01/12/2026	01/12/2027	01/12/2028	01/12/2029	01/12/2030	01/12/2031	01/12/2032	01/12/2033
17	Jan	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296		Year	01/12/2024
18	Feb	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296		Month	May
19	Mar	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
20	Apr	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296		Result	0.0377
21	May	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
22	Jun	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296		Manual formula	0.0377
23	Jul	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
24	Aug	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
25	Sep	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
26	Oct	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
27	Nov	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
28	Dec	0.1085	0.0507	0.0195	0.0214	0.0271	0.0294	0.0296	0.0296	0.0296	0.0296	0.0296	0.0296
Sheet1

Cell Formulas
Range	Formula
P5,P20	P5	=LET(rng,B2:M13,dts,B1:M1,mnth,A2:A13,AVERAGE(VSTACK(OFFSET(OFFSET(rng,,MATCH(P2,dts,0)-1,,1),1,,MATCH(P3,mnth,0)),OFFSET(OFFSET(rng,,MATCH(P2,dts,0)-2,,1),MATCH(P3,mnth,0),,12-MATCH(P3,mnth,0)))))
P7	P7	=AVERAGE(E5:E13,F2:F4)
P22	P22	=AVERAGE(C22:C28,D17:D21)

Apologies for my tardy reply. It seems that when you select Dec as a reference month the model do not calculate the average value. Apart from this it is perfect! Many thanks for your valuable assistance!!