+ Reply to Thread
Results 1 to 2 of 2

Aggregate PIVOT TABLE with custom Cumulative Product formula

  1. #1
    Registered User
    Join Date
    11-23-2020
    Location
    US
    MS-Off Ver
    2016
    Posts
    1

    Aggregate PIVOT TABLE with custom Cumulative Product formula

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

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,031

    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

Bookmarks

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