Multiplying data of range, then skip a cell and multiply for another range

[ohidur]

I want to multiply the data of Bangladesh Finance Limited every 11 months and skip 12 months. For instance, I want to multiply the data from January to November of 2015, then Skip December and then again multiply the data from January to November of 2016 and continue that for the rest of the years. Would you please help me with the formula?

Multiply.xlsx
	A	B	C	D	E	F	G	H
1			Bangladesh Finance Limited	Bay leasing	Delta brac housing finance	Agni Systems	Daffodil Com	BD Industrial Fin
2	Year	Month
3	2015	Jan	0.92	0.96	1.10	1.03	1.09	1.03
4		Feb	0.94	1.02	1.04	1.02	1.02	0.97
5		Mar	0.88	0.91	1.00	1.03	0.93	0.85
6		Apr	0.78	0.87	0.83	0.78	0.85	0.81
7		May	1.18	1.05	1.23	1.27	1.11	1.35
8		Jun	0.95	0.91	1.05	0.87	1.11	0.66
9		Jul	1.20	1.02	1.09	0.99	1.04	1.05
10		Aug	0.86	0.94	1.06	0.94	1.31	0.91
11		Sep	1.24	1.09	1.03	1.00	0.95	1.02
12		Oct	0.78	0.88	0.99	0.82	1.24	0.86
13		Nov	1.03	1.05	1.16	1.19	0.92	1.36
14		Dec	1.16	1.08	1.06	1.01	1.09	0.99
15	2016	Jan	1.59	1.07	1.97	1.16	1.23	1.05
16		Feb	0.83	1.04	0.78	0.91	1.02	1.07
17		Mar	1.07	0.90	1.02	0.99	0.85	0.80
18		Apr	0.79	0.81	0.97	0.90	0.96	0.91
19		May	1.17	1.08	1.01	1.08	1.18	1.00
20		Jun	0.96	1.03	1.02	1.04	0.99	0.96
21		Jul	1.01	1.02	1.04	0.92	0.91	0.97
22		Aug	1.04	1.02	1.02	1.02	1.02	1.03
23		Sep	1.26	1.24	0.98	1.06	1.21	1.06
24		Oct	0.69	0.96	0.97	0.92	1.22	0.95
25		Nov	1.09	1.10	1.04	1.20	1.09	1.03
26		Dec	1.31	1.16	1.00	1.06	1.28	1.33
27	2017	Jan	1.18	1.11	1.05	1.08	0.92	0.94
28		Feb	1.06	0.99	1.01	1.06	1.01	0.96
29		Mar	0.95	1.17	1.13	0.99	0.92	1.00
30		Apr	1.09	0.86	0.93	1.04	1.17	0.97
31		May	1.23	1.02	1.03	1.11	0.90	1.02
32		Jun	1.10	0.95	1.00	0.97	1.07	1.03
33		Jul	0.90	1.12	0.99	1.06	1.25	1.11
34		Aug	1.04	1.00	1.11	0.98	0.95	1.00
35		Sep	0.99	0.95	1.01	0.94	0.94	1.03
36		Oct	1.04	1.02	0.99	0.91	1.13	0.94
37		Nov	1.07	1.02	1.06	1.04	0.93	0.97
38		Dec	0.99	0.99	1.02	0.96	0.93	1.06
39	2018	Jan	1.08	0.99	0.97	0.95	0.96	0.93
40		Feb	0.92	0.94	1.01	1.02	0.92	1.00
41		Mar	0.94	0.95	1.02	0.98	0.85	0.91
42		Apr	0.94	1.15	1.01	0.98	1.16	1.04
43		May	1.03	0.82	1.00	0.98	0.97	0.71
44		Jun	0.93	0.98	0.98	0.97	1.07	0.99
45		Jul	0.87	0.93	1.03	1.20	1.11	0.97
46		Aug	1.25	1.08	1.01	0.98	0.97	0.98
47		Sep	0.86	0.88	0.98	0.87	0.87	0.97
48		Oct	1.18	0.95	1.00	0.97	0.87	0.85
49		Nov	0.95	0.99	0.96	1.21	1.12	0.98
50		Dec	1.05	1.21	1.01	0.97	1.25	1.20
51	2019	Jan	1.19	1.03	1.05	1.04	1.09	1.08
52		Feb	0.84	0.89	1.07	0.99	1.04	0.85
53		Mar	0.92	0.95	1.04	0.93	1.03	1.02
54		Apr	0.95	0.97	0.92	0.99	1.09	0.91
55		May	1.22	1.15	0.99	1.05	0.92	1.15
56		Jun	0.94	0.91	1.02	0.97	0.98	0.87
57		Jul	0.87	0.80	0.97	0.95	1.13	0.42
58		Aug	1.02	1.01	0.99	1.01	1.04	1.41
59		Sep	1.02	1.00	1.02	0.87	0.97	0.97
60		Oct	0.76	0.96	0.98	0.90	0.95	0.77
61		Nov	1.11	1.14	1.00	1.11	1.09	1.78
62		Dec	0.99	0.91	0.94	0.95	1.21	0.68
63	2020	Jan	0.89	0.97	0.98	0.96	0.88	0.86
64		Feb	1.00	0.97	1.05	1.12	1.04	1.04
65		Mar	0.89	0.85	0.92	0.83	1.09	1.08
66		May	1.00	1.00	1.00	1.00	1.00	0.96
67		Jun	1.00	1.00	1.00	1.00	0.97	1.00
68		Jul	1.00	1.02	1.00	1.31	1.01	1.00
69		Aug	1.61	1.50	1.00	1.18	1.34	1.50
70		Sep	1.96	1.27	1.00	1.00	0.99	1.74
71		Oct	0.91	1.01	1.00	0.87	0.91	0.69
72		Nov	1.06	0.99	1.00	0.95	0.99	0.89
73		Dec	1.27	1.21	1.00	1.10	1.17	1.10
Sheet1

Cell Formulas
Range	Formula
C3:H73	C3	=1+'[Templted.xlsx]Monthly Actual Return'!C4

 

[Eric W]

Welcome to the MrExcel forum!

Here are 2 ways to do it:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M
1			Bangladesh Finance Limited	Bay leasing	Delta brac housing finance	Agni Systems	Daffodil Com	BD Industrial Fin		11-month product		Year	11-month product
2	Year	Month										2015	0.851533
3	2015	Jan	1.08	1.2	0.96	1.06	1	0.82		0.851533		2016	0.65927
4		Feb	1.16	1.03	1.08	0.8	0.88	1.03				2017	0.801276
5		Mar	0.99	1.09	0.94	1.02	0.9	1.16				2018	1.458189
6		Apr	0.86	1.13	1.18	0.84	0.87	0.86				2019	1.677538
7		May	1	0.98	1.13	1.2	0.83	0.87				2020	0.642308
8		Jun	1.11	0.92	1.07	1.2	0.9	1
9		Jul	0.8	1.02	0.99	1.2	1.2	0.88
10		Aug	0.85	0.81	0.96	1.04	1.19	0.88
11		Sep	1.04	1	1.15	1.08	1.04	1.11
12		Oct	0.9	0.99	0.97	1.04	0.93	0.82
13		Nov	1.13	1	0.84	1.05	1.11	0.91
14		Dec	1.18	1.01	1.02	1.15	0.81	0.94
15	2016	Jan	0.87	0.95	0.89	1.07	1.01	1.04		0.65927
16		Feb	0.9	1.19	1.13	0.99	0.91	1.13
17		Mar	1.1	1.18	0.9	0.93	1.16	1.07
18		Apr	0.81	0.99	0.95	1.18	1.04	0.96
19		May	1.03	0.93	0.9	1.16	1.15	1.04
20		Jun	1.16	0.97	1.1	1	1.1	0.94
21		Jul	1.14	0.84	0.9	1.08	0.92	1.12
22		Aug	1.07	0.86	0.95	1.13	0.96	1.14
23		Sep	0.93	0.91	1	0.9	0.9	1.11
24		Oct	0.83	0.84	0.85	0.92	0.83	1.08
25		Nov	0.84	0.91	1.19	1.2	0.97	1.2
26		Dec	1.12	1	0.84	0.91	0.88	0.98
27	2017	Jan	0.9	1.1	0.98	0.9	0.88	0.99		0.801276
28		Feb	1.04	0.95	0.86	0.82	1.02	1.04
29		Mar	0.95	0.82	0.8	1.2	1.17	0.92
30		Apr	0.9	0.93	1.06	1.12	1.15	1.01
31		May	1.17	1.01	1.12	0.93	1.09	0.89
32		Jun	1.08	1.16	0.99	0.86	0.81	0.88
33		Jul	1.03	0.81	1.01	1.2	0.93	1.08
34		Aug	1	0.85	0.98	1.18	0.93	0.87
35		Sep	0.88	1.09	1.2	1.07	1	0.8
36		Oct	0.93	0.86	0.91	0.89	1.09	1.12
37		Nov	0.94	0.86	1	1.02	0.91	0.99
38		Dec	0.96	1.05	0.94	0.9	0.87	0.87
39	2018	Jan	1.2	0.9	0.98	1.13	1	0.91		1.458189
40		Feb	1.14	0.98	0.9	0.84	1.19	1.05
41		Mar	0.85	1.2	0.93	1.18	1.19	0.88
42		Apr	1.17	1.01	1.06	1.15	1.08	0.89
43		May	1.07	1.16	1	1.18	1.04	0.86
44		Jun	0.96	0.87	1.13	0.84	0.95	1.09
45		Jul	0.97	1.13	0.95	0.98	1.19	0.99
46		Aug	1.08	0.97	1.1	0.93	0.94	1.06
47		Sep	1.19	1.13	0.95	0.81	1.07	0.9
48		Oct	0.9	0.88	0.93	0.91	0.88	0.97
49		Nov	0.93	1.08	0.88	0.92	0.83	1.03
50		Dec	0.9	1.07	1.13	0.86	0.92	0.83
51	2019	Jan	0.81	0.91	0.93	1.12	1.01	1.09		1.677538
52		Feb	1.07	1.16	0.9	0.87	1.09	1.02
53		Mar	1.19	1.18	1.08	1.05	0.83	0.86
54		Apr	1.16	1.03	0.9	1.1	0.96	0.8
55		May	0.96	0.8	0.91	0.97	0.97	0.81
56		Jun	0.92	1.07	1.18	1.03	1.15	0.99
57		Jul	1.12	0.88	1.11	1.07	1.16	1.13
58		Aug	1.01	1.09	1.14	0.95	1.09	0.9
59		Sep	1.15	0.86	1.1	0.86	1.11	1
60		Oct	1.13	0.85	0.96	0.91	1.1	0.96
61		Nov	1.08	1.01	0.98	0.9	0.91	1.07
62		Dec	0.83	0.87	0.93	0.85	0.99	0.88
63	2020	Jan	0.88	0.9	1.01	1.19	0.82	1.05		0.642308
64		Feb	0.99	1.07	1.16	1.03	1.07	1.03
65		Mar	0.99	0.8	0.94	0.8	1.14	1.05
66		May	0.85	0.97	0.82	1.07	0.89	1.06
67		Jun	1.07	0.88	1.09	0.91	1.05	0.83
68		Jul	0.97	0.87	1.08	0.9	0.81	1.11
69		Aug	0.96	1.13	1.01	0.95	0.85	0.87
70		Sep	0.98	0.93	0.85	0.84	1.2	1.06
71		Oct	1.16	0.83	1.06	1.07	1.18	0.99
72		Nov	0.85	0.9	0.8	0.82	1.02	0.84
73		Dec	0.91	1.18	1.12	1.11	0.92	0.97
74
Sheet2

Cell Formulas
Range	Formula
L2:M7	L2	=CHOOSE({1,2},FILTER(A3:A100,A3:A100<>""),SUBTOTAL(6,OFFSET(C3,SEQUENCE(COUNTA(A3:A100),,0,12),0,11)))
J3:J73	J3	=IF(A3<>"",PRODUCT(C3:C13),"")
Dynamic array formulas.

Note that your numbers did not copy since your mini-sheet was pointing to another sheet which you didn't provide, so I just put in some random numbers. Hope this helps!

 

[ohidur]

=CHOOSE({1,2},FILTER(A3:A100,A3:A100<>""),SUBTOTAL(6,OFFSET(C3,SEQUENCE(COUNTA(A3:A100),,0,12),0,11)))

If I want to apply the formula to other columns as well by dragging, what changes should be made to the formula?

 

[Eric W]

Mainly by adding $ signs to the A column references, but I also broke out the year portion of the SPILL formula:

Book1 (version 1).xlsb
	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	11-month product, BFL	BL	Dbhf	Agni	DC	BD		Year	11-month product, BFL	BL	Dbhf	Agni	DC	BD
2								2015	0.851533	1.122684	1.23829	1.539922	0.789101	0.470187
3	0.851533	1.122684	1.23829	1.539922	0.789101	0.470187		2016	0.65927	0.598663	0.73621	1.635143	0.897627	2.167598
4								2017	0.801276	0.518227	0.856532	1.091124	0.91477	0.624506
5								2018	1.458189	1.269941	0.798254	0.789082	1.320973	0.661943
6								2019	1.677538	0.777751	1.144018	0.805041	1.374701	0.640783
7								2020	0.642308	0.529973	0.866813	0.667829	0.85514	0.824962
8
9
10
11
12
13
14
15	0.65927	0.598663	0.73621	1.635143	0.897627	2.167598
16
17
Sheet2

Cell Formulas
Range	Formula
Q2:Q7	Q2	=FILTER(A3:A100,A3:A100<>"")
R2:W7	R2	=SUBTOTAL(6,OFFSET(C3,SEQUENCE(COUNTA($A3:$A100),,0,12),0,11))
J3:O17	J3	=IF($A3<>"",PRODUCT(C3:C13),"")
Dynamic array formulas.