I am trying to create a pivot table which will average percentages by various categories in my source data. The applicable columns in my source data are the following:
  • Net Acquisition
  • Rental Revenue
  • Sell Price
  • Age
  • Interest Cost
  • Maintenance Cost
  • ROI %

ROI % is the only formula in the list of columns above. ROI % is calculated as such:

ROI % = ((Rental Revenue + Sell Price - Net Acquisition - Interest Cost - Maintenance Cost)/Net Acquisition) / (Age / 12)

Originally I put ROI % into a Pivot Table and discovered that Excel will not correctly calculate an Average ROI %. It will simply either SUM or AVERAGE the actual percentage numbers instead of calculating a true average. After researching in this forum, I discovered that I can insert a formula in a Calculated Field. Initially, this seemed like the solution, however when I calculate the average manually, the Average ROI % is different. Excel is somehow calculating the average incorrectly. The good news is that the Calculated Field in the Pivot table correctly calculates ROI % on a row-by-row basis, but when I go to the Grand Total or create groupings of my data, the Group Total and the Grand Total are both incorrect. I have ROI % in my source data that I can use to check if the Pivot Table Calculated Field is performing the calculating correctly row by row.

I have a suspicion as to the cause, but I cannot figure out a solution. When calculating the ROI % on a given row, the formula is as I've listed it above. When calculating the Average ROI % on a group of rows, the formula is the same, but you must SUM the data in each individual column except the Age column. You must AVERAGE the Age. So when calculating a group of rows, I would use the same formula, but with the following data points:
  • SUM Net Acquisition
  • SUM Rental Revenue
  • SUM Sell Price
  • AVERAGE Age
  • SUM Interest Cost
  • SUM Maintenance Cost

To prove this, I created two new Calculated Fields in the Pivot Table, one for the numerator and one for the denominator using the formula above:

Calc GP Gain % = ((Rental Revenue + Sell Price - Net Acquisition - Interest Cost - Maintenance Cost)/Net Acquisition)
Calc Annualized Age = (Age / 12)

Both of these calculated correctly for the grouping subtotals and for the Grand Total when compared to their respective manual Grand Total calculation.

Next I created a new calculated field called Calc ROI % as follows:

Calc ROI % = Calc GP Gain % / Calc Annualized Age

The number I get is incorrect for both the grouping subtotals and the Grand Total. One other suspicion I have is that maybe Excel PIVOT cannot successfully calculate an average on a formula that divides a percentage by a percentage.

Any recommendations on how I can calculate AVERAGE ROI % in a PIVOT Table?