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