+ Reply to Thread
Results 1 to 2 of 2

Aggregate PIVOT TABLE with custom Cumulative Product formula

  1. #1
    Registered User
    Join Date
    MS-Off Ver

    Aggregate PIVOT TABLE with custom Cumulative Product formula


    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.

    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.
    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.
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    MS-Off Ver
    Excel 2016

    Re: Aggregate PIVOT TABLE with custom Cumulative Product formula

    Standard Pivot Table can not use "custom calculation" (except power PT which I am not familar with)

    I offer a table instead.

    (It can refer to DayEnd only if needed)

    See attachment.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to used pivot table to aggregate this request?
    By darbebo in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 07-05-2016, 01:44 PM
  2. Replies: 0
    Last Post: 04-24-2014, 05:27 PM
  3. Pivot Table disable aggregate sum, keep sum only? confused!
    By cesarmontoya in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 08-15-2013, 02:19 PM
  4. Replies: 3
    Last Post: 12-28-2012, 08:27 AM
  5. Custom formula in pivot table
    By raym0nd in forum Excel General
    Replies: 8
    Last Post: 08-18-2010, 05:22 AM
  6. pivot table to aggregate values
    By excelism in forum Excel General
    Replies: 1
    Last Post: 07-13-2010, 08:54 AM
  7. Pivot Table custom formula
    By pmguerra in forum Excel General
    Replies: 1
    Last Post: 03-10-2009, 10:15 AM

Tags for this Thread


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1