Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Pivot Table - Dynamic Field
Is it possible to add a "dynamic" field to a Pivot Table? For example: I have two columns A and B. I want the dynamic field to show B-A. More specifically, I'd like the Delta (B-A) column to show the growing total of the difference. A---B--Delta 0---1----1 3---4----2 0---0----2 1---1----2 1---7----8 2---3----9 I know a database would be better for this, and I know there are options with VB scripting; however, just assume neither of those can be done. Thanks, JC -- carlyman ------------------------------------------------------------------------ carlyman's Profile: http://www.excelforum.com/member.php...o&userid=22440 View this thread: http://www.excelforum.com/showthread...hreadid=399225 |
#2
|
|||
|
|||
If these are separate fields in the pivot table, you can create a
calculated field: On the pivot toolbar, choose PivotTableFormulas Calculated Field Type Delta as the field name For the formula, type: =B-A Click OK In the pivot table, right-click on the heading for the Delta field Choose Field Settings Click the Options button From the Show Data As dropdown, choose Running Total In For the Base Field, choose your Row field Click OK carlyman wrote: Is it possible to add a "dynamic" field to a Pivot Table? For example: I have two columns A and B. I want the dynamic field to show B-A. More specifically, I'd like the Delta (B-A) column to show the growing total of the difference. A---B--Delta 0---1----1 3---4----2 0---0----2 1---1----2 1---7----8 2---3----9 I know a database would be better for this, and I know there are options with VB scripting; however, just assume neither of those can be done. Thanks, JC -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#3
|
|||
|
|||
Yes. First, as I read your question, you'll need a row field in your list
(or other data source) with unique values so that each underlying record appears in a different row of the pivot table, but if you're grouping records and want your Delta based on groups then unique values aren't needed. Then, create the table with one of your existing fields as the Data Field. If necessary, select a cell in the table. Display the Pivot Table toolbar and click the PivotTable dropdown at its left end. Choose Formulas from the menu and Calculated Field from the Formulas menu. Give the field a name and as its formula supply B-A. Click Add, then click OK. Right-click a cell containing the newly calculated field value in the Data Area, and choose Field Settings. Click the Options button. In the Show Data As dropdown, choose Running Total In, and as the Base Field choose the field that identifies the tables rows. Now, if you want, you can get rid of the original Data Field. C^2 Conrad Carlberg -- Excel Sales Forecasting for Dummies, Wiley, 2005 -- Excel Sales Forecasting for Dummies, Wiley, 2005 "carlyman" wrote in message ... Is it possible to add a "dynamic" field to a Pivot Table? For example: I have two columns A and B. I want the dynamic field to show B-A. More specifically, I'd like the Delta (B-A) column to show the growing total of the difference. A---B--Delta 0---1----1 3---4----2 0---0----2 1---1----2 1---7----8 2---3----9 I know a database would be better for this, and I know there are options with VB scripting; however, just assume neither of those can be done. Thanks, JC -- carlyman ------------------------------------------------------------------------ carlyman's Profile: http://www.excelforum.com/member.php...o&userid=22440 View this thread: http://www.excelforum.com/showthread...hreadid=399225 |
#4
|
|||
|
|||
Hi Debra -- ya got there first!
Regards, C^2 Conrad Carlberg -- Excel Sales Forecasting for Dummies, Wiley, 2005 "Debra Dalgleish" wrote in message ... If these are separate fields in the pivot table, you can create a calculated field: On the pivot toolbar, choose PivotTableFormulas Calculated Field Type Delta as the field name For the formula, type: =B-A Click OK In the pivot table, right-click on the heading for the Delta field Choose Field Settings Click the Options button From the Show Data As dropdown, choose Running Total In For the Base Field, choose your Row field Click OK carlyman wrote: Is it possible to add a "dynamic" field to a Pivot Table? For example: I have two columns A and B. I want the dynamic field to show B-A. More specifically, I'd like the Delta (B-A) column to show the growing total of the difference. A---B--Delta 0---1----1 3---4----2 0---0----2 1---1----2 1---7----8 2---3----9 I know a database would be better for this, and I know there are options with VB scripting; however, just assume neither of those can be done. Thanks, JC -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a Pivot Table Calculated Field to get a Unique Count | Excel Worksheet Functions | |||
How to get pivot table Time field to appear correctly | Excel Worksheet Functions | |||
How can I show all field data in a pivot table, instead of blank | Excel Discussion (Misc queries) | |||
Pivot table, dynamic data formula | Excel Discussion (Misc queries) | |||
Problems with Pivot Table Field Sorting in Excel 2002 | Excel Discussion (Misc queries) |