Custom formulas in Pivot Table

[Valiant108]

In the Pivot Report the subtotal and total function works well except in
calculating the % variance in an income statement. The report tools
calculate the sum of each line % but what I want as a sub-total and grand total
is the total actual dollar minus the total budget dollars divided by the total
budget dollars for the total variance percentage. Not the column sub total
and grand total. How do I put a custom formula in for Excel 2007?

[Ken Puls]

Hi there, and welcome to the forum!

You know, it's interesting that you should ask this question... I JUST published a video course this week that covers this topic. If you're interested it is currently on an introductory special... you can find out more about it here.

To your question, what I would do is insert a new calculated field so that it gives me the variances for each line as well as the subtotals. To do that:

• Go to PivotTable Tools --> Options
• Select Fields, Items & Sets --> Calculated Field

Set the values as follows:

• Name: Budget
• Formula: =(Actual-Budget)/Budget

Now click Add, and you should be done.

Hope it helps,

[Valiant108]

You misunderstood, I have turned on Pivot Table subtotals and grand total and I don't like the subtotal for only the total variance. Excel takes the column total
and not the sub total actual and sub total budget to calculate the overall subtotal for that group. I want to overwrite the Excel group
subtotal with a custom subtotaling formula. Yet everything I have tried is refused by the program. How do I do it?

[Ken Puls]

Okay, just to be clear here...

Is the variance worked out by the PivotTable, or is in your source data?

Is there any way that you can upload a sample (with falsified numbers) of what it looks like now, vs what you want it to look like? It doesn't have to be a PivotTable, just a range of hard data is fine. (Double click the Reply to Thread button to attach a workbook.)