Cumlative returns by date

[jacklan]

[TABLE="width: 500"]
<tbody>[TR]
[TD]01/11/09
[/TD]
[TD]1.12
[/TD]
[/TR]
[TR]
[TD]01/12/09[/TD]
[TD]2.23[/TD]
[/TR]
[TR]
[TD]01/01/10[/TD]
[TD]1.45[/TD]
[/TR]
[TR]
[TD]01/02/10[/TD]
[TD]-1.68[/TD]
[/TR]
[TR]
[TD]01/03/10[/TD]
[TD]2.01[/TD]
[/TR]
[TR]
[TD]01/04/10[/TD]
[TD]-1.77[/TD]
[/TR]
[TR]
[TD]01/05/10[/TD]
[TD]1.34[/TD]
[/TR]
[TR]
[TD]01/06/10[/TD]
[TD]1.76[/TD]
[/TR]
[TR]
[TD]01/07/10[/TD]
[TD]2.34

[/TD]
[/TR]
[TR]
[TD]01/08/10[/TD]
[TD]-1.23[/TD]
[/TR]
[TR]
[TD]01/09/10[/TD]
[TD]1.76[/TD]
[/TR]
[TR]
[TD]01/10/10[/TD]
[TD]2.45[/TD]
[/TR]
[TR]
[TD]01/11/10[/TD]
[TD]-1.56
[/TD]
[/TR]
[TR]
[TD]01/12/10[/TD]
[TD]1.65[/TD]
[/TR]
[TR]
[TD]01/01/11[/TD]
[TD]2.34[/TD]
[/TR]
[TR]
[TD]01/02/11[/TD]
[TD]1.90[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]I have a table like the above, but larger 2009-2017, and want to calculate yearly cumulative returns (where RHS % returns) is there a simple way to do this, currently playing round with

=AVERAGEIFS(Returns!B2:B98,Returns!A2:A98,">=01-Jan-2015",Returns!A2:A98,"<=31-Dec-2015")

but I would like the cumulative yearly retuen, not the yearly average.

Any help appreciated!! [/TD]
[TD]


[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

Rather than "hard coding" the date/year in the formula, in which case you'll need a different formula for every year, would this work for you:

Book1
	A	B
1	1/11/2009	1.12
2	1/12/2009	2.23
3	1/1/2010	1.45
4	1/2/2010	-1.68
5	1/3/2010	2.01
6	1/4/2010	-1.77
7	1/5/2010	1.34
8	1/6/2010	1.76
9	1/7/2010	2.34
10	1/8/2010	-1.23
11	1/9/2010	1.76
12	1/10/2010	2.45
13	1/11/2010	-1.56
14	1/12/2010	1.65
15	1/1/2011	2.34
16	1/2/2011	1.9
Returns

Book1
	A	B
1	Year	Cumulative Return
2	2009	3.35
3	2010	8.52
4	2011	4.24
Sheet3

Cell Formulas
Range	Formula
B2	=SUMPRODUCT((YEAR(Returns!A$1:A$16)=A2)*(Returns!B$1:B$16))

B2 formula copied down for all years you need, adjust cell references as needed.
Let me know if you prefer to "hard code" the date/year in the formula(s).