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

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

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

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