Rolling 12 month using =PRODUCT function

[NinaE_11]

Hello,
I'm looking use the product function in lieu of the below =SUM function, using the XLOOKUPs. Here is the function that works when summing the values:

=SUM(XLOOKUP(K19,$C$15:$C$214,$D$15:$D$214) :XLOOKUP(K20,$C$15:$C$214,D15:D214))

However the data I am working with are portfolio performance returns, and I typically use the below function to link the returns for a 12 month period: (using the Array brackets):

{=PRODUCT(1+D162:D173)-1}

If I simply try to replace 'PRODUCT' in lieu of 'SUM' in first formula (with normal 1+ and array brackets, the formula chokes.

I have a list of monthly returns from 2006, and would like the user to enter an month-end date in a cell, using +EOMONTH AND =XOOKUP formulas to find 12 months prior, link those 12 returns to month-end date.

Any advice would be much appreciated - thank you!

 

[Anonymous1378]

Try

=PRODUCT(IF(($C$15:$C$214>=K19)*($C$15:$C$214<=EOMONTH(K19,12)),$D$15:$D$214,""))

 

[NinaE_11]

Try

=PRODUCT(IF(($C$15:$C$214>=K19)*($C$15:$C$214<=EOMONTH(K19,12)),$D$15:$D$214,""))

Thank you for your efforts - unfortunately, I couldn't get this to work.

 

[Anonymous1378]

Thank you for your efforts - unfortunately, I couldn't get this to work.

Try inputting it with Ctrl-Shift-Enter instead of Enter? Should not be a problem if you have XLOOKUP access though, which is definitely not 2016.
To clarify, column C contains month end dates, K19 contains your starting month end date, and column D contains the values you want to multiply?

 

[NinaE_11]

Try inputting it with Ctrl-Shift-Enter instead of Enter? Should not be a problem if you have XLOOKUP access though, which is definitely not 2016.
To clarify, column C contains month end dates, K19 contains your starting month end date, and column D contains the values you want to multiply?

I do have XLOOKUP Access. I tried entering the array brackets with the Ctrl+Shift+Enter method. Result = 0.00%

You have it all correct: Column C = Month end Dates; Column D = monthly returns in % and K19 holds the starting month end period. For example :
Column D
0.98%
3.59%
1.67%
1.45%
1.75%
5.15%
-0.97%
-3.16%
4.48%
5.72%
6.33%
-7.31%

{=PRODUCT(1+D1:D12)-1} = 20.52%

 

[Anonymous1378]

I amended the "" in the formula to 0, perhaps it will work for you?

Book21
	C	D	E	F	G	H	I	J	K
14								92.67%
15	31-Jan-2000	2.55%						92.67%
16	29-Feb-2000	6.14%
17	31-Mar-2000	2.31%
18	30-Apr-2000	5.68%
19	31-May-2000	2.97%							31-05-2000
20	30-Jun-2000	3.18%
21	31-Jul-2000	6.01%
22	31-Aug-2000	8.55%
23	30-Sep-2000	7.64%
24	31-Oct-2000	2.41%
25	30-Nov-2000	3.26%
26	31-Dec-2000	2.07%
27	31-Jan-2001	7.07%
28	28-Feb-2001	6.09%
29	31-Mar-2001	8.83%
30	30-Apr-2001	4.01%
31	31-May-2001	5.49%
32	30-Jun-2001	5.05%
33	31-Jul-2001	8.22%
34	31-Aug-2001	6.17%
35	30-Sep-2001	1.21%
36	31-Oct-2001	0.27%
37	30-Nov-2001	3.96%
38	31-Dec-2001	5.11%
39	31-Jan-2002	3.83%
40	28-Feb-2002	8.65%
41	31-Mar-2002	3.20%
42	30-Apr-2002	8.55%
43	31-May-2002	5.24%
44	30-Jun-2002	9.81%
45	31-Jul-2002	2.56%
46	31-Aug-2002	6.11%
47	30-Sep-2002	6.51%
48	31-Oct-2002	8.40%
49	30-Nov-2002	8.53%
50	31-Dec-2002	0.90%
51	31-Jan-2003	9.50%
52	28-Feb-2003	1.08%
53	31-Mar-2003	6.52%
54	30-Apr-2003	9.77%
55	31-May-2003	1.65%
56	30-Jun-2003	7.70%
57	31-Jul-2003	3.45%
58	31-Aug-2003	2.65%
59	30-Sep-2003	1.46%
60	31-Oct-2003	8.95%
61	30-Nov-2003	6.53%
62	31-Dec-2003	6.26%
63	31-Jan-2004	5.88%
64	29-Feb-2004	8.36%
65	31-Mar-2004	5.08%
66	30-Apr-2004	3.29%
67	31-May-2004	8.71%
68	30-Jun-2004	3.37%
69	31-Jul-2004	0.94%
70	31-Aug-2004	6.94%
71	30-Sep-2004	3.03%
72	31-Oct-2004	5.16%
73	30-Nov-2004	9.94%
74	31-Dec-2004	2.90%
75	31-Jan-2005	7.13%
76	28-Feb-2005	8.54%
77	31-Mar-2005	9.11%
78	30-Apr-2005	7.93%
79	31-May-2005	1.53%
80	30-Jun-2005	2.35%
81	31-Jul-2005	8.22%
82	31-Aug-2005	1.59%
83	30-Sep-2005	3.24%
84	31-Oct-2005	4.32%
85	30-Nov-2005	9.46%
86	31-Dec-2005	5.69%
87	31-Jan-2006	3.16%
88	28-Feb-2006	3.62%
89	31-Mar-2006	8.20%
90	30-Apr-2006	6.64%
91	31-May-2006	6.50%
92	30-Jun-2006	9.43%
93	31-Jul-2006	3.58%
94	31-Aug-2006	9.87%
95	30-Sep-2006	2.42%
96	31-Oct-2006	2.71%
97	30-Nov-2006	9.11%
98	31-Dec-2006	6.90%
99	31-Jan-2007	7.24%
100	28-Feb-2007	2.26%
101	31-Mar-2007	9.27%
102	30-Apr-2007	1.12%
103	31-May-2007	4.74%
104	30-Jun-2007	0.72%
105	31-Jul-2007	0.33%
106	31-Aug-2007	6.61%
107	30-Sep-2007	0.41%
108	31-Oct-2007	6.18%
109	30-Nov-2007	5.56%
110	31-Dec-2007	2.33%
111	31-Jan-2008	5.14%
112	29-Feb-2008	1.39%
113	31-Mar-2008	4.46%
114	30-Apr-2008	3.46%
115	31-May-2008	7.60%
116	30-Jun-2008	1.15%
117	31-Jul-2008	0.10%
118	31-Aug-2008	3.00%
119	30-Sep-2008	9.48%
120	31-Oct-2008	4.04%
121	30-Nov-2008	9.16%
122	31-Dec-2008	2.65%
123	31-Jan-2009	1.62%
124	28-Feb-2009	6.40%
125	31-Mar-2009	3.60%
126	30-Apr-2009	9.36%
127	31-May-2009	1.91%
128	30-Jun-2009	1.82%
129	31-Jul-2009	4.33%
130	31-Aug-2009	0.58%
131	30-Sep-2009	8.03%
132	31-Oct-2009	8.00%
133	30-Nov-2009	6.14%
134	31-Dec-2009	4.91%
135	31-Jan-2010	1.24%
136	28-Feb-2010	6.32%
137	31-Mar-2010	0.08%
138	30-Apr-2010	2.01%
139	31-May-2010	2.15%
140	30-Jun-2010	4.69%
141	31-Jul-2010	9.00%
142	31-Aug-2010	4.04%
143	30-Sep-2010	5.48%
144	31-Oct-2010	7.23%
145	30-Nov-2010	0.78%
146	31-Dec-2010	9.62%
147	31-Jan-2011	4.59%
148	28-Feb-2011	8.14%
149	31-Mar-2011	5.71%
150	30-Apr-2011	7.80%
151	31-May-2011	8.66%
152	30-Jun-2011	6.64%
153	31-Jul-2011	8.96%
154	31-Aug-2011	1.54%
155	30-Sep-2011	7.02%
156	31-Oct-2011	1.91%
157	30-Nov-2011	0.12%
158	31-Dec-2011	7.25%
159	31-Jan-2012	7.94%
160	29-Feb-2012	9.07%
161	31-Mar-2012	8.84%
162	30-Apr-2012	2.53%
163	31-May-2012	6.69%
164	30-Jun-2012	6.55%
165	31-Jul-2012	9.33%
166	31-Aug-2012	9.57%
167	30-Sep-2012	7.37%
168	31-Oct-2012	9.25%
169	30-Nov-2012	7.21%
170	31-Dec-2012	1.38%
171	31-Jan-2013	7.11%
172	28-Feb-2013	2.64%
173	31-Mar-2013	4.92%
174	30-Apr-2013	9.91%
175	31-May-2013	8.16%
176	30-Jun-2013	2.32%
177	31-Jul-2013	7.34%
178	31-Aug-2013	9.73%
179	30-Sep-2013	3.15%
180	31-Oct-2013	2.49%
181	30-Nov-2013	7.00%
182	31-Dec-2013	5.52%
183	31-Jan-2014	6.31%
184	28-Feb-2014	3.19%
185	31-Mar-2014	5.94%
186	30-Apr-2014	2.62%
187	31-May-2014	0.76%
188	30-Jun-2014	4.19%
189	31-Jul-2014	5.64%
190	31-Aug-2014	5.02%
191	30-Sep-2014	4.88%
192	31-Oct-2014	5.49%
193	30-Nov-2014	2.47%
194	31-Dec-2014	1.14%
195	31-Jan-2015	9.35%
196	28-Feb-2015	0.33%
197	31-Mar-2015	6.15%
198	30-Apr-2015	5.18%
199	31-May-2015	5.25%
200	30-Jun-2015	1.74%
201	31-Jul-2015	9.01%
202	31-Aug-2015	8.80%
203	30-Sep-2015	5.20%
204	31-Oct-2015	5.75%
205	30-Nov-2015	1.46%
206	31-Dec-2015	4.55%
207	31-Jan-2016	0.05%
208	29-Feb-2016	1.02%
209	31-Mar-2016	8.46%
210	30-Apr-2016	4.32%
211	31-May-2016	3.04%
212	30-Jun-2016	6.90%
213	31-Jul-2016	7.26%
214	31-Aug-2016	2.11%
Sheet1

Cell Formulas
Range	Formula
J14	J14	=PRODUCT(IF(($C$15:$C$214>=K19)*($C$15:$C$214<=EOMONTH(K19,12)),$D$15:$D$214,0)+1)-1
J15	J15	=PRODUCT(1+D19:D31)-1

 

[NinaE_11]

THANK YOU SO MUCH!! Works Beautifully - thank you!