Product Function Use on Data

[excelfunctionneeded]

Hi, sorry to bother you folks. But been trying to solve an issue in excel. I have a series of daily returns for each year dating back to 2002 and would to calculate the monthly returns for each month end period. Instead of manually running fvschedule and adjusting each by month, is there a formula to capture and link/calulate the daily returns given the specific month end? Currently i'm turning all daily dates into month end periods to test the sumproduct function which works, but instead of summing the periods I need linkage.




For example the SUMPRODUCT(--($C:$C=F2),($B:$B)) function does just this (C:C = daily periods turned into month end dates, F2 = Month end date and B:B are the daily returns).

 

[KRice]

Here is one idea to consider...and if your rates are expressed as a percentage, you could replace the last term in the formula with: (1+($C$4:$C$11))

Book1
	B	C	D	E	F
3	Date	Daily Rates		Month Beginning	Monthly Rate
4	1/3/2021	1.01		1/1/2021	1.040502
5	1/4/2021	1.02		2/1/2021	1.0712
6	1/5/2021	1.01		3/1/2021	1.071612
7	2/15/2021	1.03
8	2/16/2021	1.04
9	3/23/2021	1.02
10	3/24/2021	1.02
11	3/27/2021	1.03
Sheet1

Cell Formulas
Range	Formula
F4:F6	F4	=PRODUCT(IF(--( DATE(YEAR($B$4:$B$11),MONTH($B$4:$B$11),1) =E4)<>0,--(DATE(YEAR($B$4:$B$11),MONTH($B$4:$B$11),1) = E4) *($C$4:$C$11)))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[StephenCrump]

Perhaps like this?

	A	B	C	D	E
1	Fri 29 Jan 2021	-0.50%
2	Mon 1 Feb 2021	-0.16%		Feb 2021	2.91%
3	Tue 2 Feb 2021	0.02%
4	Wed 3 Feb 2021	-0.61%
5	Thu 4 Feb 2021	0.23%
6	Fri 5 Feb 2021	-0.32%
7	Mon 8 Feb 2021	0.27%
8	Tue 9 Feb 2021	-0.30%
9	Wed 10 Feb 2021	-0.35%
10	Thu 11 Feb 2021	0.38%
11	Fri 12 Feb 2021	-0.86%
12	Mon 15 Feb 2021	0.42%
13	Tue 16 Feb 2021	0.99%
14	Wed 17 Feb 2021	-0.06%
15	Thu 18 Feb 2021	0.30%
16	Fri 19 Feb 2021	0.80%
17	Mon 22 Feb 2021	-0.93%
18	Tue 23 Feb 2021	0.83%
19	Wed 24 Feb 2021	0.91%
20	Thu 25 Feb 2021	0.87%
21	Fri 26 Feb 2021	0.47%
22	Mon 1 Mar 2021	0.68%
23	Tue 2 Mar 2021	0.74%
24	Wed 3 Mar 2021	-0.63%
Sheet3

Cell Formulas
Range	Formula
E2	E2	=PRODUCT(1+(A1:A24>EOMONTH(D2,-1))*(A1:A24<=EOMONTH(D2,0))*B1:B24)-1

 

[excelfunctionneeded]

thank you guys trying it! best!