Calculating Differences between columns in a Pivot Table
 

I have a worksheet that contains sales records by person. Each record
includes the month of the sale; there can be multiple records for each
salesperson for each month. Each record also has a field that indicates the
data identifies the record as Sales or Quota.

I have set up a pivot table that shows:
Salesperson, Jan2007Sales, Jan2007Quota, Feb2007Sales, Feb2007Quota,
Mar2007Sales, Mar2007Quota, ... , Total2007Sales, Total2007Quota

Is there a way to add a column after each month that shows the difference
for that month between the Sales amount and the Quota amount:
Salesperson, Jan2007Sales, Jan2007Quota, Jan2007Diff, Feb2007Sales,
Feb2007Quota, Feb2007Diff, Mar2007Sales, Mar2007Quota, Mar2007Diff, ...
, Total2007Sales, Total2007Quota, Total2007Diff

Any help is appreciated...
--
Ed G

Calculating Differences between columns in a Pivot Table
 

I think your best bet would be to create a new column in the data named
"difference" or something like that, then input a formula like =A1-B1, copy
that down. And in the pivot table you can choose to show the difference or
not. Otherwise I have no clue how to make the pivot table do it by itself
without looking at each month individually.

"Ed G" wrote:

I have a worksheet that contains sales records by person. Each record
includes the month of the sale; there can be multiple records for each
salesperson for each month. Each record also has a field that indicates the
data identifies the record as Sales or Quota.

I have set up a pivot table that shows:
Salesperson, Jan2007Sales, Jan2007Quota, Feb2007Sales, Feb2007Quota,
Mar2007Sales, Mar2007Quota, ... , Total2007Sales, Total2007Quota

Is there a way to add a column after each month that shows the difference
for that month between the Sales amount and the Quota amount:
Salesperson, Jan2007Sales, Jan2007Quota, Jan2007Diff, Feb2007Sales,
Feb2007Quota, Feb2007Diff, Mar2007Sales, Mar2007Quota, Mar2007Diff, ...
, Total2007Sales, Total2007Quota, Total2007Diff

Any help is appreciated...
--
Ed G

Calculating Differences between columns in a Pivot Table
 

try to insert a formula in the pivot you have to this for each month seperatly:

go to pivot table-- formulas-- calculated fields-- then for each month
create a formula.

"Ed G" wrote:

I have a worksheet that contains sales records by person. Each record
includes the month of the sale; there can be multiple records for each
salesperson for each month. Each record also has a field that indicates the
data identifies the record as Sales or Quota.

I have set up a pivot table that shows:
Salesperson, Jan2007Sales, Jan2007Quota, Feb2007Sales, Feb2007Quota,
Mar2007Sales, Mar2007Quota, ... , Total2007Sales, Total2007Quota

Is there a way to add a column after each month that shows the difference
for that month between the Sales amount and the Quota amount:
Salesperson, Jan2007Sales, Jan2007Quota, Jan2007Diff, Feb2007Sales,
Feb2007Quota, Feb2007Diff, Mar2007Sales, Mar2007Quota, Mar2007Diff, ...
, Total2007Sales, Total2007Quota, Total2007Diff

Any help is appreciated...
--
Ed G

Calculating Differences between columns in a Pivot Table
 

Hi Nikki. Thank you for the quick response.

Because the field that identifies the record type as "Sales" is the same one
that identifies it as "Quota", I cannot create a standard calculated formula
to do this. It would look like
=RecType - RecType


--
Ed G


"Nikki" wrote:

try to insert a formula in the pivot you have to this for each month seperatly:

go to pivot table-- formulas-- calculated fields-- then for each month
create a formula.

"Ed G" wrote:

I have a worksheet that contains sales records by person. Each record
includes the month of the sale; there can be multiple records for each
salesperson for each month. Each record also has a field that indicates the
data identifies the record as Sales or Quota.

I have set up a pivot table that shows:
Salesperson, Jan2007Sales, Jan2007Quota, Feb2007Sales, Feb2007Quota,
Mar2007Sales, Mar2007Quota, ... , Total2007Sales, Total2007Quota

Is there a way to add a column after each month that shows the difference
for that month between the Sales amount and the Quota amount:
Salesperson, Jan2007Sales, Jan2007Quota, Jan2007Diff, Feb2007Sales,
Feb2007Quota, Feb2007Diff, Mar2007Sales, Mar2007Quota, Mar2007Diff, ...
, Total2007Sales, Total2007Quota, Total2007Diff

Any help is appreciated...
--
Ed G