Array formula in Pivot

[Rudi]

Hi,

Please see attached for detailed question and sample data...

TX

[Rudi]

I have been reading up on Pivot field calcs and the article I read confirmed that arrays cannot be used (see quote below), however, is there still a solution for the worksheet version?
TX

In formulas that you create for calculated fields and calculated items, you can use operators and expressions as you do in other worksheet formulas. You can use constants and refer to data from the report, but you cannot use cell references or defined names. You cannot use worksheet functions that require cell references or defined names as arguments, and you cannot use array functions.

[sdckapr]

This is the closest I can come up initially:
in a new column (of the dataset) (labeled "Val2", change as desired) add the calc:
=LN(1+C2)
copy it down the column.

Expand the pivot to include the new column. Then in a calc field in the pivot you can use:
= EXP(Val2)-1

For single entries in the table this seems to give what you want, but for entries when you have multiple copies of date and name (like 2 C's on May 1, 1984) it will not sum the 6.66% and 5.05% to give 11.71% but will calc (1.066*1.055)- 1 to give 12.046% as the "sum" to match the "totalizing method" you are using for overall...

Steve

[HansV]

I don't think it can be done in the pivot table itself. Here is a macro to construct the formulas.

Code: Select all

Sub CreateFormulas()
    Const c = "D" ' Target column
    Dim r As Long
    Dim s As Long
    Dim m As Long
    Application.ScreenUpdating = False
    ' Preparations
    m = Range("A" & Rows.Count).End(xlUp).Row
    With Range(c & "3:" & c & m)
        .ClearContents
        .NumberFormat = "0.00%"
    End With
    ' Initialize row number
    s = m - 1
    ' Loop
    For r = m To 3 Step -1
        If IsDate(Range("A" & r).Value) Then
            ' Add formula if column A contains a date
            Range(c & r).FormulaArray = "=PRODUCT(1+B" & (r + 1) & ":B" & s & ")-1"
            s = r - 1
        End If
    Next r
    Application.ScreenUpdating = True
End Sub

[Rudi]

Steve,
Thanks for that attempt.
Your method did provide the correct values on the totals where each date group had two entries, but if it had one entry or three (and more), it was not quite a match. See image attached.
Appreciate the reply.

Spoiler

1.jpg

Hans,
It seems like the VBA route is the easiest fix for this. I appreciate the code and implemented it on the actual file. It works well. TX

[sdckapr]

The issue comes down to what you really want to calculate whether it can be done in a pivot. The calc you proposed, as Hans indicated, can not be done since it is a gemish of calcs instead of being straightforward.

The straightforward is the sum, the way I proposed is more a direct product-sum (taking your calc to the individuals, not the groups, which I thought may actually be the preferred method).

To show you what I mea, look at May 1, 1984. There are 3 values:
C: 6.66%, C: 5.05% and B 2.84%

A pivot table can give you the sum (as you had done)
The sum is: 6.66%+5.05%+2.84% = 14.55%

It can also be modified to give the "Product Sum" (as I did) :
(1+6.66%)*(1+5.05%)*(1+2.84%) - 1 = (1.0666)*(1.0505)*(1.0284) - 1 = 15.23%

What you are proposing is not individual calcs, but a composite Product-sum method:
(1+6.66%+5.05%)*(1+2.84%) - 1 = (1.1171)*(1.0284) - 1 = 14.88%

The difference amounts 2 is in the sum the values with the same name: The 2 Cs add to 11.71% (6.66%+5.05%) while in my Prod-sum method, it is:
(1+6.66%)*(1+5.05%)-1 = (1.0666)*(1.0505) -1 = 12.05%

The question is if you have these 3 values with the identical name, it would give a total sum of 14.55%. But if the names are all different you would get a total of 15.23%. For what you are doing, would you expect to get "total sums" which vary based on the name distribution or should the sum be the same in either case?

[Add on: In other words, would you expect the same totals from these 2 cases
C: 6.66%, C: 5.05%, and C: 2.84%
A: 6.66%, B: 5.05%, and C: 2.84%]

Other than a macro, the only method I can see would be to do the sum-Pivot, and then do a 2nd pivot table on the pivot table results with the intermediate LN calc on the first pivot table results, with the calc field int he 2nd pivot.

Steve

[Rudi]

Hi Steve,

The calculation is specific to calculating the return on a list of investments that are grouped be Portfolio. This is AFAIK. It is not my calc. It is what is used in the original workbook that we are remodeling.
BTW: I got the Pivot data back to front. In the sample file I set up the table grouped by date with A, B and C as sub categories. The actual data is Portfolio (iow the A, B, C and sub group by date - where there are not duplicates in the dates as it is by month.) I don't know if that will make a difference to your proposed solution, but I thought I'd let you know. As for your lengthy reply above, I understand the arguments you raise based on having individual sums and not sub-sums; but the maths involved and the resulting output is a bit beyond me.

Cheers and TX again for your expertise.

[sdckapr]

I look at it as you have an "operation" [I will label it (ps)]. You do that operation on 2 values (call them A and B):
Per how you define that operation for some cases as :
A (ps) B = (1+A)*(1+B) - 1

But in other cases:
A (ps) B <> (1+A)*(1+B) - 1 but instead A (ps) B = A+B

Which is where I am confused as to the overall total since the math seems to change with the situation ...

I suppose the issue is I understand Math, but don't understand what the numbers actually represents and what the "Total" is meant to represent either...

Steve

[Rudi]

TX. The results of the expression I originally posted is accurate based on the original workbook, and the same expression in the code Hans set up is working well for this purpose. I don't want to mess with something that is currently providing the correct and expected results, so I'll stick with the code. Appreciate your support as always.

[sdckapr]

Not a problem. My "concern" wasn't the operation once the pivot table was created, but actually the summations in creating the pivot table itself. That is the real source of the disagreement in methodology. Numbers from a date with the same name are operated on differently than numbers on the same date with different names. But if it gets you what you want/expect my "concerns" are irrelevant.

Take care,
Steve