How to get weighted average from pivot table? SUMPRODUCT breaks when pivot table grows.

I have a pivot table that has DATE as the rows and TENURE as the columns. I need to get a weighted average of each date's tenures. So in the column just outside the pivot table I create a SUMPRODUCT formula for each row, something like...

=SUMPRODUCT(E110:GI110,$E$109:$GI$109)/GJ110

I then copy-paste this formula manually to the last row of the pivot table, which WILL be constant. This works great, until the next time I refresh the data and the pivot table grows horizontally (more tenure values) and has to replace the values/formulas in my SUMPRODUCT column. How (and where to put) a SUMPRODUCT formula that will recalculate (and NOT get destroyed) when refreshing the data? Is there a better way to get a weighted average (NOT straight average) for the rows in a pivot table?

TIA. Dan

Hi,

Can you not do it by adding a "Calculated Field" within the Pivot Table?

I've been trying numerous formulas to do this with no avail. Any suggestions?

Can you post a sample excel file here with enough data without any confidential/sensitive info?

meybe read this WV with PT