I have a sheet with 4 columns, let us say A through D having following values
Region-id, product-id, sales-amount, sales-quantity. Each unique region-id
can have multiple product-ids including duplicates of a product-id. Each
unique product-id can belong to several region-id, including duplicates of a
region-id.

I would like to construct a 2-dimensional pivot table where one dimension
(Pivot jargon = ROW) has region-id and another dimension (Pivot jargon =
COLUMN) has product-id. At the intersection of each row and column (Pivot
jargon = DATA) I would like to have the result of the following formula:

=(total of all sales-amount for that region-id and product-id) / (total of
all sales-quantity for that region-id and product-id).

I can do this using other worksheet formula, but I was wondering if I can do
it with pivot tables?

Any ideas?

You could create a calculated formula:

On the pivot table toolbar, choose PivotTableFormulasCalculated Field
Type a name for the field
In the Formula box, type:
=IF('sales qty'=0,0,'sales amount'/'sales qty')
substituting your field names
Click OK

DKS wrote:
I have a sheet with 4 columns, let us say A through D having following values
Region-id, product-id, sales-amount, sales-quantity. Each unique region-id
can have multiple product-ids including duplicates of a product-id. Each
unique product-id can belong to several region-id, including duplicates of a
region-id.

I would like to construct a 2-dimensional pivot table where one dimension
(Pivot jargon = ROW) has region-id and another dimension (Pivot jargon =
COLUMN) has product-id. At the intersection of each row and column (Pivot
jargon = DATA) I would like to have the result of the following formula:

=(total of all sales-amount for that region-id and product-id) / (total of
all sales-quantity for that region-id and product-id).

I can do this using other worksheet formula, but I was wondering if I can do
it with pivot tables?

Any ideas?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Thanks, that works.

"Debra Dalgleish" wrote:

You could create a calculated formula:

On the pivot table toolbar, choose PivotTableFormulasCalculated Field
Type a name for the field
In the Formula box, type:
=IF('sales qty'=0,0,'sales amount'/'sales qty')
substituting your field names
Click OK

DKS wrote:
I have a sheet with 4 columns, let us say A through D having following values
Region-id, product-id, sales-amount, sales-quantity. Each unique region-id
can have multiple product-ids including duplicates of a product-id. Each
unique product-id can belong to several region-id, including duplicates of a
region-id.

I would like to construct a 2-dimensional pivot table where one dimension
(Pivot jargon = ROW) has region-id and another dimension (Pivot jargon =
COLUMN) has product-id. At the intersection of each row and column (Pivot
jargon = DATA) I would like to have the result of the following formula:

=(total of all sales-amount for that region-id and product-id) / (total of
all sales-quantity for that region-id and product-id).

I can do this using other worksheet formula, but I was wondering if I can do
it with pivot tables?

Any ideas?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html