Offset, Product & Year

[tanvirabid]

Hi people,
I was hoping to get help on Offset, Product and Year combined. I have monthly returns. To annualize for 12 months, I can use =((PRODUCT(1+B4:B15)-1)) to get the result. Column B has the results.
I want the result to be based on the Years i.e. 2020, 2019, 2018. Since I have many years of monthly data and a new monthly row is added every month. The dates are in column A.
So I wanted to build a dynamic formula using Offset and Year to get the Product result. The purpose of using offset is that new months will be added below row 3.
I am using this formula:
=PRODUCT(IF(YEAR(OFFSET($A$3,1,0,20,1))=$D5,(1+OFFSET(B$3,1,0,20,1))-1))
For instance, the answer for 2020 should be 0.12228
The idea is that Product result should be based on months that belong to the Year 2020, 2019 and so on. However, the formula is not working even with array.
Please see the image.
Thanks a lot.

 

[Habtest]

I think the brackets in the end are misplaced, does this work?

=PRODUCT(IF(YEAR(OFFSET($A$3,1,0,20,1))=$D5,(1+OFFSET(B$3,1,0,20,1))))-1

 

[tanvirabid]

I think the brackets in the end are misplaced, does this work?

=PRODUCT(IF(YEAR(OFFSET($A$3,1,0,20,1))=$D5,(1+OFFSET(B$3,1,0,20,1))))-1

I think it's working. You're a genuis! Thanks a million.

 

[maabadi]

Because of 12 month of each year I take 12 for height at offset section.
Try this:

Book1
	A	B	C	D	E	F
1
2
3
4	Dec-20	0.1		Annual
5	Nov-20	0.11		2020	4.606015
6	Oct-20	0.12		2019	17.3743
7	Sep-20	0.13		2018	53.11205
8	Aug-20	0.14		2017	144.7465
9	Jul-20	0.15		2016	362.9578
10	Jun-20	0.16
11	May-20	0.17
12	Apr-20	0.18
13	Mar-20	0.19
14	Feb-20	0.2
15	Jan-20	0.21
16	Dec-19	0.22
17	Nov-19	0.23
18	Oct-19	0.24
19	Sep-19	0.25
20	Aug-19	0.26
21	Jul-19	0.27
22	Jun-19	0.28
23	May-19	0.29
24	Apr-19	0.3
25	Mar-19	0.31
26	Feb-19	0.32
27	Jan-19	0.33
28	Dec-18	0.34
29	Nov-18	0.35
30	Oct-18	0.36
31	Sep-18	0.37
32	Aug-18	0.38
33	Jul-18	0.39
34	Jun-18	0.4
35	May-18	0.41
36	Apr-18	0.42
37	Mar-18	0.43
38	Feb-18	0.44
39	Jan-18	0.45
40	Dec-17	0.46
41	Nov-17	0.47
42	Oct-17	0.48
43	Sep-17	0.49
44	Aug-17	0.5
45	Jul-17	0.51
46	Jun-17	0.52
47	May-17	0.53
48	Apr-17	0.54
49	Mar-17	0.55
50	Feb-17	0.56
51	Jan-17	0.57
52	Dec-16	0.58
53	Nov-16	0.59
54	Oct-16	0.6
55	Sep-16	0.61
56	Aug-16	0.62
57	Jul-16	0.63
58	Jun-16	0.64
59	May-16	0.65
60	Apr-16	0.66
61	Mar-16	0.67
62	Feb-16	0.68
63	Jan-16	0.69
Sheet2

Cell Formulas
Range	Formula
E5:E9	E5	=((PRODUCT(1+OFFSET(INDIRECT("B" & MATCH(D5,YEAR($A$1:$A$63),0)),0,0,12,1))-1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Habtest]

I think it's working. You're a genuis! Thanks a million.

No problem, thanks for the feedback.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Offset, Product & Year
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[tanvirabid]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Offset, Product & Year
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

My apologies. Thanks, Will comply in future.