Product Function Use on Data

excelfunctionneeded

New Member
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).

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

KRice

Well-known Member
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
BCDEF
3DateDaily RatesMonth BeginningMonthly Rate
41/3/20211.011/1/20211.040502
51/4/20211.022/1/20211.0712
61/5/20211.013/1/20211.071612
72/15/20211.03
82/16/20211.04
93/23/20211.02
103/24/20211.02
113/27/20211.03
Sheet1
Cell Formulas
RangeFormula
F4:F6F4=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

MrExcel MVP
Perhaps like this?

ABCDE
1Fri 29 Jan 2021-0.50%
2Mon 1 Feb 2021-0.16%Feb 20212.91%
3Tue 2 Feb 20210.02%
4Wed 3 Feb 2021-0.61%
5Thu 4 Feb 20210.23%
6Fri 5 Feb 2021-0.32%
7Mon 8 Feb 20210.27%
8Tue 9 Feb 2021-0.30%
9Wed 10 Feb 2021-0.35%
10Thu 11 Feb 20210.38%
11Fri 12 Feb 2021-0.86%
12Mon 15 Feb 20210.42%
13Tue 16 Feb 20210.99%
14Wed 17 Feb 2021-0.06%
15Thu 18 Feb 20210.30%
16Fri 19 Feb 20210.80%
17Mon 22 Feb 2021-0.93%
18Tue 23 Feb 20210.83%
19Wed 24 Feb 20210.91%
20Thu 25 Feb 20210.87%
21Fri 26 Feb 20210.47%
22Mon 1 Mar 20210.68%
23Tue 2 Mar 20210.74%
24Wed 3 Mar 2021-0.63%
Sheet3
Cell Formulas
RangeFormula
E2E2=PRODUCT(1+(A1:A24>EOMONTH(D2,-1))*(A1:A24<=EOMONTH(D2,0))*B1:B24)-1

excelfunctionneeded

New Member
thank you guys trying it! best!

Replies
1
Views
460
Replies
5
Views
476
Replies
3
Views
308
Replies
0
Views
202
Replies
0
Views
393

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,826
Messages
5,766,669
Members
425,367
Latest member
Boboka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back