Pivot calc field

[MPH]

I have a pivot table based on table with 4 cols:

Region > 10 different regions

Period > month of year, expressed as 1 thru'12

Value > any value

BA > B [for Budget], A [for Actual]

Table is 000's of rows.

Pivot shows 2 value columns for A and also B.

That's the easy bit.

Want another column in the pivot showing the difference between B & A, so if
B=1000 & A=950, the new column will show 50.

Have searched help, the web and books.

Still haven't a solution.

Is there a solution?

Should be simple - shouldn't it?

Help please.

 

[steven1001]

I have never been able to resolve this without creating two additiona
columns, Actual and Budget, and either a column called variance or
calculated field for variance.

Assume Column B has the actual/budget flag and Column C has th
$value.
Populate the the new Actual column with something like
=if(b2="A",C2,0) ... and equivalent for the new budget column.

If there is a better way I am keen to know it also. It get even mor
fun if you want month and year-to-date in same pivot report :-(

regards.

 

[kletcho]

You should use a calculated item. To do this right click on BA, then
go to Formulas, then Calulated Item. Fill the formula B - A into the
formula bar and enter a name. Then format to thousands if desired.

 

[MPH]

Many thanks, that works.

However.
My data table is ~11000 long, in 16 columns. I am getting "there are too
many records to complete this operation" messages.
I have had to reduce the number of fields in the Pivot, to avoid that
message.
This is the first time I have come up against that message. Is there any
way of avoiding it?
I am using XL 2003 on 1.8 ghz AMD Athlon 64 + 1Gb memory.

 

[kletcho]

This is from Microsoft's website on Excel 2000 limitations. I couldn't
find one for Excel 2003.

Feature Maximum limit
------- -------------

PivotTable reports Limited by available memory
on a sheet

Items in a PivotTable 8,000
report

Row or column fields Limited by available memory
in a PivotTable report

Page fields in a 256 (may be limited by available memory)
PivotTable report

Data fields in a 256
PivotTable report

Calculated item formulas Limited by available memory
in a PivotTable report



If you are limited by memory then you may need to use steven1001's
suggestion to put a helper column in.

 

[steven1001]

I think the problem may be that a calculated field is what is required,
not a calculated item. I have never been able to create a calculated
field based on the different values stored in a single column of data,
hence the need to create the "helper" columns.

Suggestions?

regards..