New to Pivot Tables - how add calculated field 1

[gwog]

Hi I have a pivot table whose source data comes from Access 97.

My Row is Item
My Column is SDATE
My Data is:

sum of histfcst
sum of histqty
sum of abs


What I need to do is add two calculated data fields:

FA - which would use the following formula:
1-(sum of abs/sum of histqty)

Consumption - which would use this formula:
sum of histqty/sum of histfcst

I just can't figure out how to add fields and use a formula as the source for the data.

Any help is greatly appreciated.

thanks.

Lisa.

ps. I'm using Excel 2002

 

[SkipVought]

Lisa,

I am a BIG PivotTable proponent. But PivotTables have some limitations -- and this is one of them.

Here's what I would do.

1. In the PT Wizard - Layout, drag the rows and columns OFF, leaving JUST THE DATA AREA. -- Finish.

2. Double click IN the resulting PT -- this gives you a source table.

3. Add the calculated fields to the new source data

4. Pivot on the new source data.

VOLA!

Skip,

http://www.TheOfficeExperts.com

 

[pbrodsky]

in excel 97, you can right-click in the pivot table, then select formulas -> Calculated Field... from the pop-up menu. You can enter a field name and simple formulas to create calculated fields. They will show up as fields in the layout wizard.

 

[SkipVought]

pbrodsky,

Thanx! I am still learning!

Skip,

http://www.TheOfficeExperts.com