I've got a pivot table that has date down the side and sales, costs and margin as the values. I want to add another value that is margin/sales that changes as and when the pivot does.
I've tried using the custom calculations but I'm not having much luck - any ideas?
Howdy. There are at least three ways to handle it.
1. Doing calculations in Pivot Tables with formulas can be a challenge and I normally don't recommend it. To do that, once your Pivot table is created, then on the Pivot toolbar, click on the dropdown on the left and choose Formulas > Calculated Fields. Then int he resultant window create the calculation you want.
2. Put the percents in the original table and then use that as basis for calculating int he Pivot Table. It will take a little putzing around with it to get what you want.
3. Better way is to use VBA code to create the Pivot Table, then use further code to copy/past special values and add whatever other calculations you need done. This is by far the more complete solution because you are not limited to what Pivot Tables provide. It takes a little to get it set up, but it is also very fast, and gives you great flexibility. For help in this Bill Jelen, et al (in VBA and Macros for MS Excel in chapter 12 offer code samples to do each step and how to make it all dynamic, even with the creation of Pivot Tables. I have used this and found it to be the best solution.
________ Cl125
