Customing Excel Pivot Table Calculations

I recently had a chance to work on a pivot table project, which turned out to be one of the most complicated things I’ve ever done in Excel.  The requirements:

  • An Excel pivot table output, where user can choose rows and columns in any combination
  • At each level, depending on whether the level directly above it has a certain relationship with that field, then the calculation would change dynamically

To simplify, here was the logic: if the two fields were related then the field below should be an average and the field above should be a sum of the averages.  But if the fields were not related, then each level should be a sum.  Field mapping was set up in a separate table.

That’s Impossible??!!

Well, it seemed impossible, but after some thinking and experimented, we cracked it.  We set up a two way system.  In the MS Access back end, we set up a number of calculated fields.  In Excel, we set up VBA to dynamically switch the pivot calculation, based on the mapping table.


  1. Sales Person (Customer (Uploaded Date: In this scenario, there will be average on Date level, then sum of Date average on Customer level and finally sum of Customer level. Etc.





View Our Impressive List of Clients
Back to Home Page
Contact Us for a Free Quote