Archive of Mr Excel Message Board


Back to Pivot Tables in Excel archive index
Back to archive home

Calculation based on running total in Pivot table

Posted by G Patterson on February 04, 2001 5:44 PM
To illustrate my problem:


Nov Dec
Col_A 14 15
Col_B 20 25
Col_C 20 45 (running total of Col_B)
Col_D 70% 33%(Col_A/Col_C)

Col_C must be calculated in the pivot table.
I can get Col_C to be displayed as a running total,
but the underlying field still contains the individual
numbers. This results in Col_D dividing by the same
value as Col_B, not the running total number from Col_C.

Any Suggestions?


Re: Calculation based on running total in Pivot table

Posted by Mark W. on February 05, 2001 9:00 AM
G", I struggled a bit with Col_D myself. I'd like to
say that it can't be done in a PivotTable as a Calc-
ulated Field (but, I'm sure as soon as these words leave
my mouth someone will prove me wrong). However, I would
like to pose an alternative (work around) which may
not live up to your expectations, but gets the job done.

Suppose that cells A1:C3 contain:
{"Month","Col_A","Col_B";37196,14,20;37226,15,25}.
I trust that I'm interpreting your illustrative data list
correctly! You'll notice that my Month column contains
datevalues rather than text such as "Nov", "Dec", etc.
For my solution to work these must be datevalues. As such
you'll need to apply some formatting to your data list and
PivotTable to make them appear as Nov, Dec, etc.

If you enter "Col_D" into cell D1, the formula,
=B2/SUMIF(A:A,"<="&A2,C:C), into D2 and copy down to
cell D3, then (after extending the PivotTable's data range)
you can add the field Col_D to your existing PivotTable
using Sum as its summary function.


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.