Custom Calculation in Grand Total in pivot tables

[yossis]

Hi,

I created a macro which creates a pivot table.

a screenshot is attached (or the URL to the image is View image: screen)

My problem is that i need the Sum of CPL totals to not do the actual totals but rather a custom calculation.

CPL refers to Spending divided by Registration. For example, the CPL for the cell A72 is 336.31 because Spending is 336.31 and the amount of Registrations is 1. so 336.31/1 equals 336.31. Ignition One is the name of a website where Spending is how much money i spent on advertising on this website and Registrations are the amount of users who registered from my ads. As you can see the grand total for spending for Ignition One is 3141.33 and the number of registrations is 9 (which is really bad but anyway As i mentioned the CPL=spending/registrations which is in our case 3141.33/9=349.03.

Instead the grand total for CPL just sums up all intermediary CPLs per smaller units like individual ads etc.

Is there any way to make grand totals not sum (and not average) but actually do the calculation (CPL=spending/registrations)?

Thank you!

 

[yossis]

I forgot to mention that I'm using Windows 7, Excel 2013 but the solution has to be compatible with Excel 2010.

 

[Andrew Poulsom]

Did you try adding a Calculated Field for CPL (giving it another name)?

 

[yossis]

I just changed the name but this didn't help because the sum function still applies. I'm not sure if understood you correctly though please see the screenshot View image: calculated field

 

[Andrew Poulsom]

On the Options tab under PivotTable Tools on the Ribbon click Fields, Items & Sets and choose Calculated Field. There you can use the formula:

=Spending /Registration

 

[yossis]

Andrew, thank you so much!!! You absolutely saved me.

 

[yossis]

By the way, can you use excel functions inside calculated fields (e.g. round, iferror etc.)? I have to admit i already tried and it didn't work.

 

[Andrew Poulsom]

No, sorry.