Adding a Formula as a data variable in a pivot

[SanCarlosCyclist]

Is it possible to create a formula as data column in a pivot table?
For eamaple let's say I have the following Table:

Name Billed Charges Paid Charges
George 1000 500
Pilar 2000 750
Fred 1211 602

I would like to add a data variable of [Billed Charges] - [Paid
Charges] and another [Billed Charges] / [Paid Charges] -1

I know that one way to do this is to add columns to the raw data and
refresh the pivot, but I am wondering whether I can create a
calculated formula as a data column in a pivot table. Thanks much for
your help.

 

[Dave Peterson]

I'd start at Debra Dalgleish's site:
http://contextures.com/xlPivot10.html

Is it possible to create a formula as data column in a pivot table?
For eamaple let's say I have the following Table:

Name Billed Charges Paid Charges
George 1000 500
Pilar 2000 750
Fred 1211 602

I would like to add a data variable of [Billed Charges] - [Paid
Charges] and another [Billed Charges] / [Paid Charges] -1

I know that one way to do this is to add columns to the raw data and
refresh the pivot, but I am wondering whether I can create a
calculated formula as a data column in a pivot table. Thanks much for
your help.

 

[SanCarlosCyclist]

I'd start at Debra Dalgleish's site:http://contextures.com/xlPivot10.html

Is it possible to create a formula as data column in a pivot table?
For eamaple let's say I have the following Table:

Name         Billed Charges      Paid Charges
George         1000                      500
Pilar             2000                       750
Fred             1211                       602

I would like to add a data variable of [Billed Charges] - [Paid
Charges] and another [Billed Charges] / [Paid Charges] -1

I know that one way to do this is to add columns to the raw data and
refresh the pivot, but I am wondering whether I can create a
calculated formula as a data column in a pivot table. Thanks much for
your help.

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave, thanks so much for the link to Debra's site. I played with a few
of my pivot tables, and all of Debra's examples worked fine. Here is
my question though. Can I create a field in a pivot table based on any
formula? For example, can I create a trend formula (a/b - 1), or a
custom formula like Variable a / Variable b * 12000?

I am used to doing this in Microsoft Access queries but I have never
used custom formulas in excel pivot tables. Since ultimately, my final
output is in an excel pivot table, it would be great to build those
formulas right into the table. That would save me a few steps. Any
suggestions are appreciated.

 

[Dave Peterson]

I'm not sure it fits your needs (do all that you want), but did you look at
Debra's site for GetPivotData?

http://contextures.com/xlPivot06.html

If I can't get my custom calc's to work, I'll usually add those extra formulas
to the raw table--or convert the pivottable to plain old data (or copy|paste
values in a different sheet) and write my own formulas against that.

If I have to recreate that process, I'd use a macro to do the work.

I'd start at Debra Dalgleish's site:http://contextures.com/xlPivot10.html

Is it possible to create a formula as data column in a pivot table?
For eamaple let's say I have the following Table:

Name Billed Charges Paid Charges
George 1000 500
Pilar 2000 750
Fred 1211 602

I would like to add a data variable of [Billed Charges] - [Paid
Charges] and another [Billed Charges] / [Paid Charges] -1

I know that one way to do this is to add columns to the raw data and
refresh the pivot, but I am wondering whether I can create a
calculated formula as a data column in a pivot table. Thanks much for
your help.

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave, thanks so much for the link to Debra's site. I played with a few
of my pivot tables, and all of Debra's examples worked fine. Here is
my question though. Can I create a field in a pivot table based on any
formula? For example, can I create a trend formula (a/b - 1), or a
custom formula like Variable a / Variable b * 12000?

I am used to doing this in Microsoft Access queries but I have never
used custom formulas in excel pivot tables. Since ultimately, my final
output is in an excel pivot table, it would be great to build those
formulas right into the table. That would save me a few steps. Any
suggestions are appreciated.

 

[Jason]

The Debra Dalgelish article that Dave has referred you to does actually
show you how to do the two things you originally specified, namely:

I would like to add a data variable of [Billed Charges] - [Paid
Charges] and another [Billed Charges] / [Paid Charges] -1

The first one is a simple 'Difference From' custom calc, and the second
is a '% Difference From'.

Regarding your other examples, such as a/b * 12000, these can be
achieved through the use of 'Calculated fields', see the following url
for an example:

http://www.ozgrid.com/Excel/pivot-calculated-fields.htm

HTH....Jason
---

I'd start at Debra Dalgleish's site:http://contextures.com/xlPivot10.html

Is it possible to create a formula as data column in a pivot table?
For eamaple let's say I have the following Table:
Name Billed Charges Paid Charges
George 1000 500
Pilar 2000 750
Fred 1211 602
I would like to add a data variable of [Billed Charges] - [Paid
Charges] and another [Billed Charges] / [Paid Charges] -1
I know that one way to do this is to add columns to the raw data and
refresh the pivot, but I am wondering whether I can create a
calculated formula as a data column in a pivot table. Thanks much for
your help.

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave, thanks so much for the link to Debra's site. I played with a few
of my pivot tables, and all of Debra's examples worked fine. Here is
my question though. Can I create a field in a pivot table based on any
formula? For example, can I create a trend formula (a/b - 1), or a
custom formula like Variable a / Variable b * 12000?

I am used to doing this in Microsoft Access queries but I have never
used custom formulas in excel pivot tables. Since ultimately, my final
output is in an excel pivot table, it would be great to build those
formulas right into the table. That would save me a few steps. Any
suggestions are appreciated.

 

[SanCarlosCyclist]

The Debra Dalgelish article that Dave has referred you to does actually
show you how to do the two things you originally specified, namely:

 >>> I would like to add a data variable of [Billed Charges] - [Paid
 >>> Charges] and another [Billed Charges] / [Paid Charges] -1

The first one is a simple 'Difference From' custom calc, and the second
is a '% Difference From'.

Regarding your other examples, such as a/b * 12000, these can be
achieved through the use of 'Calculated fields', see the following url
for an example:

http://www.ozgrid.com/Excel/pivot-calculated-fields.htm

HTH....Jason
---

I'd start at Debra Dalgleish's site:http://contextures.com/xlPivot10.html
SanCarlosCyclist wrote:
Is it possible to create a formula as data column in a pivot table?
For eamaple let's say I have the following Table:
Name         Billed Charges      Paid Charges
George         1000                       500
Pilar             2000                       750
Fred             1211                      602
I would like to add a data variable of [Billed Charges] - [Paid
Charges] and another [Billed Charges] / [Paid Charges] -1
I know that one way to do this is to add columns to the raw data and
refresh the pivot, but I am wondering whether I can create a
calculated formula as a data column in a pivot table. Thanks much for
your help.

Dave, thanks so much for the link to Debra's site. I played with a few
of my pivot tables, and all of Debra's examples worked fine. Here is
my question though. Can I create a field in a pivot table based on any
formula? For example, can I create a trend formula (a/b - 1), or a
custom formula like Variable a / Variable b * 12000?

I am used to doing this in Microsoft Access queries but I have never
used custom formulas in excel pivot tables. Since ultimately, my final
output is in an excel pivot table, it would be great to build those
formulas right into the table. That would save me a few steps. Any
suggestions are appreciated.- Hide quoted text -

- Show quoted text -

Hi Jason, it worked great. thanks so much. This saves me many steps.