Pivot Table custom calculations

[Jeffrey]

I'm trying to incorporate a custom calculation in a pivot
table, but have been unable to get it to work. I checked
Excel's help under "About formulas in PivotTable and Pivot
Chart reports" and thought the section within:
-Formula syntax
-Field names in item references
seemed like the right approach. Any help would be greatly
appreciated.

The pivot table is set up similar to this:


Shipped Ordered Forecast
Sales Cost Sales Cost Sales Cost
Customer
Cust 1 10 5 20 12 32 15
Cust 2 20 11 25 15 40 12
Cust 3 15 8 15 10 30 15

My row field contains 'Customer'. My column field
is 'Sales Status', which includes (Shipped, Ordered, and
Forecast). My data field includes (Sales and Cost). Can
I include a formula that will specify certain fields and
items? For instance, I would like to create a formula
that will calculate the difference in margin between the
sum of (Shipped and Ordered) and the Forecast. Another
simple example of a formula would be (Shipped
Sales+Ordered Sales)-(Forecast Sales).

 

[mzehr]

Hi Jeffery,
Set up your pivot table Layout with the Customer in the
Row Area, and the ShippedSales, OrderedSales, and
ForecastSales in the Data Area. Once your pivot table is
finished, right click on one of the sales columns, select
Formulas - Calculated Field. Once the Insert Calculated
Field box comes up, insert a name, then in the formula box
put =ShippedSales+OrderedSales-ForecastSales.

HTH

 

[Guest]

Thanks for your attention on this. I'm not sure if I
wasn't totally clear when describing my pivot table setup
or if I'm just not understanding your directions, but I
need a little more clarification.

First let me elaborate on my fields. For instance, I do
not have a "ShippedSales" field. I have a "Sales Status"
field that includes 'Shipped', 'Ordered', and 'Forecast'.
My data field includes Sales and Cost. This allows me to
see Shipped as one category, and within Shipped I can see
what the sales and costs are. To illustrate again, I will
show how my pivot table is setup:

Shipped Ordered Forecast
Sales Cost Sales Cost Sales Cost
Customer
Cust 1 10 5 20 12 32 15
Cust 2 20 11 25 15 40 12
Cust 3 15 8 15 10 30 15


Let me know if your previous directions still apply. If
so I will continue trying to figure it out.

Thanks.