Hi,
I have this dataset (attached excel file) - Daily Dates and Daily Returns of the stock. I added End of Month, End of Quarter, End of Year because I do {=PRODUCT(1+"RANGE")-1} based on the date to sum up by Month, Quarter, Year but for my ideal solution I shouldn't need those extra columns.
Data.jpg
Columns H, L and P have correct values I need. It uses dynamic PRODUCT(IF(...)...)-1 but the formula itself is in columns I, M, Q (just for reference).
I would like to do the same thing in the PIVOT table and I spent hours trying to figure out custom "Calculated Field" feature, because it seems that this is the right way solve my problem. However no matter how much I tried I can't make the formula aggregate correctly.
Field.jpg
You can see it in J14 through J23. The correct values are in K14 through K23 (same as my calcs in columns H, L, P). I think I missing something regarding the "array". For PRODUCT formula to work you need to use CTRL+SHIFT+ENTER instead of just ENTER. This seems like an easy aggregation but I guess I am doing something wrong.
The closest I could get to the solution is creating separate column with 1+"returns" and aggregate that column by PRODUCT instead of SUM. But this leaves me with a 100% (or 1) and I can't have it. I know I can just create another column and subtract it from PIVOT's column but I can't use that because I have to get the final numbers in the PIVOT.
I can't seem to find an answer anywhere at all.
Thank you.
Bookmarks