Pivot Table - Dynamic Field

[carlyman]

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]

If these are separate fields in the pivot table, you can create a
calculated field:

On the pivot toolbar, choose PivotTable>Formulas >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

[Conrad Carlberg]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Conrad Carlberg]

Hi Debra -- ya got there first!

Regards,

C^2
Conrad Carlberg

--
Excel Sales Forecasting for Dummies, Wiley, 2005

"Debra Dalgleish" <[email protected]> wrote in message
news:[email protected]...
> If these are separate fields in the pivot table, you can create a
> calculated field:
>
> On the pivot toolbar, choose PivotTable>Formulas >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
>