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.

 

1

 

 

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