Pivot Table: Running total percentage of an absolute value?

Question

In Excel 2010 I wish to input a Pivot Table to create a value that is "% running total / estimate at that point of time". I can create a "running total", but I'm not able to divide it correctly with the "estimate at that point of time". The value much preferably be calculated within the Pivot Table and no changes to the input data format is allowed.

The example demonstrates better than words. The field I need help with creating in the Pivot Table is marked with ** ** ** ** ** ** ** **.

I've created a worksheet with the example: https://www.dropbox.com/s/4a2f66n6uxfikte/11649309%20Example%20pivot-table-running-total-percentage-of-an-absolute-value.xlsx

Thanks in advance.

Input Table

Input data is in the following format - no changes are allowed. For each month I have number of hours spent in that month and the estimate for the entire project at that point of time (not just for the month).

Month      Hours spent   Estimate
01-2012              1         50
02-2012             10         50
03-2012              5         60
04-2012             35         60

The Pivot Table I wish to create

                                                 ** ** ** ** ** ** ** **
Month           Hours spent   Estimate                    % Hrs/Estimate 
            (running total)                      (How do I create this?)
01-2012                   1         50                                2%
02-2012                  11         50                               22%
03-2012                  16         60                               27%
04-2012                  51         60                               85%

Clean-up: 9 years later I find my own question again and see that I've never resolved it. I must admit that I can't completely recall the case where this was relevant. Hence I'll accept the most likely answer to complete the question... Sorry :-) — sumokadet, Jun 08 '21 at 12:53

score 1 · Answer 1 · answered Jul 25 '12 at 14:12

Here's what you can do:

You have your Month, Hours spent, and Estimate columns laid out in columns A-C. In column D, you put in this formula to get the "Hours spent (running total)" column:

=SUM($B$2:B2)

And of course, drag that down until the data runs out. In column E, you put in this formula to get the "% Hrs/Estimate" column:

=D2 / C2

Again, drag that down until the data runs out. Copy/paste column A into column F. Then highlight columns C-F, hit Alt + D + P, then click Finish, and the layout for your new pivot table will appear on a new sheet. You can then play around with the PivotTable Field List to get what you want.

Ross Larson · Accepted Answer · 2012-07-25T14:58:57.030

Edit: I misread one detail, this requires the running total column to exist already in "hours spent"

Or to just calculate in PivotTable (NOT adding to dataset)

PivotTable Tools Ribbon => Options Tab => Tools Group => Formulas => Calculated Field

Enter Name for field (NOT what you want as display name. Field name and display name MUST be different. I repeat, MUST be different, excel will not allow them to be the same).

Enter your forumla = hours spent / estimate but using the insert field options in dialog. then click Ok. Add the field if not already, change it to display as percentage and change display name to what you want it to be.

Edit: To get running total via pivot table (NOTE these two solutions WILL NOT work together, unfortunately the calculated field I outlined above will use the original data, not the running total)

right click on cell in pivot table in hours column (not running total yet) => value field settings => show values as => running total in => experiment with whatever choice works, but it should be what your row label is. (probably month)

Pivot Table: Running total percentage of an absolute value?

2 Answers2