Microsoft Office Tutorials and References
In Depth Information
Figure 20-28. Calculation = xlRunningTotal
Each cell is replaced by that cell's percentage of the sum of the values in the entire table
(for the given data field).
The formula to compute the value in a cell is:
new value = (current value * grand total) / (row total * column total)
Figures 20-28 through 20-31 illustrate these calculations, starting with the pivot table in Figure
20-10 . Note that the percent calculations require that the grand row and column totals be displayed.
The code for Figure 20-28 is:
ActiveSheet.PivotTables("Sales&Trans"). _
PivotFields("Sale").Calculation = xlRunningTotal
Referring to Figure 20-28 , cell D6 contains sales for Boston, cell D10 contains total sales for
Boston and Los Angeles, and cell D14 contains total sales for Boston, Los Angeles, and New
York. (I have had reports that the Calculation property does not always work properly. For some
reason, it may simply cause the relevant cells to fill with Excel's infamous #N/A symbols. Indeed,
I have had this same experience at times, but I have not been able to figure out why.)
Figure 20-28. Calculation = xlRunningTotal
The code for Figure 20-29 is:
ActiveSheet.PivotTables("Sales&Trans"). _
PivotFields("Sale").Calculation = xlPercentOfRow
Figure 20-29. Calculation = xlPercentOfRow
Search JabSto ::

Custom Search