Rolling year-to-date

[pholt33]

I am trying to calculate YTD but getting the sum rather than the product. How can I get the product?

Here is what I have...

YTD (G8) : =SUMPRODUCT( ( F$5:F8 ) * ( YEAR(A$5:A8 ) = YEAR($A8) ) )

1 Year (H8) : =PRODUCT(1 + $F5:$F8) - 1

At year-end dates, YTD should = 1 year

Here is a small sample of data, it continues quarterly until current date.

					3 Mo	YTD	1 Year
3/31/2013					24.70%	24.70%	-
6/30/2013					1.44%	26.14%	-
9/30/2013					-2.88%	23.27%	-
12/31/2013					10.37%	33.64%	35.61%

The Xl2bb mini sheet process was freezing my computer so here is a screenshot...

 

[awoohaw]

I'm confused. In Column H are you asking for Percent Of Year To for each quarter?

Book1
	A	B	C	D	E	F	G	H
1
2
3
4						3 Mo	YTD	1 Year
5	2013-03-31					24.70%	24.70%	73.45%
6	2013-06-30					1.44%	26.14%	77.73%
7	2013-09-30					-2.88%	23.26%	69.16%
8	2013-12-31					10.37%	33.63%	100.00%
9
Sheet1

Cell Formulas
Range	Formula
G5:G8	G5	=SUMPRODUCT((F$5:F5)*(YEAR(A$5:A5)=YEAR(A$8)))
H5:H8	H5	=SUMPRODUCT((F$5:F5)*(YEAR(A$5:A5)=YEAR(A$8)))/ SUMPRODUCT($F$5:$F$8*(--(YEAR($A$5:$A$8))=YEAR(A5)))

 

[pholt33]

Maybe this will help to show the basic mechanics of how the YTD column should work. I am looking for a generic formula so that I dont have to manually adjust the ranges each quarter/year.

 

[AhoyNC]

Maybe:
Copy formula down as needed.

Book2
	A					F	G	H	I	J
1
2
3						Returns
4						3 Mo	YTD incorrect	YTD Correct
5	3/31/2013					24.70%	24.7096	24.70% -PRODUCT(I + FS) - 1		24.70%
6	6/30/2013					1.44%	26.14%	26.50% =PRODUCT(I + F5:F6) - 1		26.50%
7	9/30/2013					-2.88%	23.2796	22.86% -PRODUCT(I + F5:F7) - 1		22.85%
8	12/31/2013					10.37%	33.64%	35.61% =PRODUCI(I + f5:f8) - 1		35.59%
9	3/31/2014					1.45%	1.45%	1.45% -PRODUCT(I + F9) - 1		1.45%
10	6/30/2014					2.20%	3.65%	3.68% PRODUCT(I + F9:F10) • 1		3.68%
11	9/30/2014					3.29%	6.94%	7.09% -PRODUCI(I + 69:611) - 1		7.09%
12	12/31/2014					8.60%	15.54%	16.30% =PRODUCT(I + F9:F12) - 1		16.30%
Sheet1

Cell Formulas
Range	Formula
J5:J12	J5	=PRODUCT(1+IF(YEAR(A5)=YEAR($A$5:A5),$F$5:F5))-1

 

[pholt33]

Beautiful, thank you!

 

[AhoyNC]

You're welcome. Thanks for the feedback.