Yes I tried the copy & pasting your formula including the single quotes but
get the same error.

I'm not sure the table you built is the same as mine. The source data would
look like this

Code LoS DIABETIC
X358 1.2 Diabetic
X357 2.2 Diabetic
X358 1.8 Non Diabetic
A559 3.4 Diabetic
X367 0.3 Non Diabetic
X358 0.9 Non Diabetic
A559 3.4 Diabetic

etc

So one column to flag Diabetics, as opposed to seperate Diabetic and Non
Diabetic columns. These two values (Diabetic/ Non Diabetic) or what gives me
my two columns in the Pivot.

Is that how yours was set out?

If so, I don't know what else I could have done wrong.


"Roger Govier" wrote:

Hi Matt

I set myself up a very small table with your data following your first
posting with headings of OPCS, Diabetic and Non Diabetic.
I then created the PT and inserted a calculated field as described, and
all worked well.
Did you enclose the Non Diabetic in single quotes?

The formula I posted, is one that I copied (using Ctrl+C) from the pane
in the setup of Insert Field.
Try copying my formula and pasting with Ctrl+V into the setup pane. If
it worked for me, then it should for you.

--
Regards

Roger Govier


"Matt D Francis" wrote in
message ...
Hi Roger and thanks for the quick reply. I'm afraid that didn't work
though.

I got a "your formula contains an error" message and it reverted to
the
Calculated Field wizard highlighting the word Non of Non Diabetic in
the
formula. I typed it as you did. Is naming the field DIABETIC confusing
things
as this also one of the two possible values found in that column?

Matt

"Roger Govier" wrote:

Hi Matt

Create a Calculated field called Difference. Use the formula
=AVERAGE(Diabetic)-AVERAGE('Non diabetic' )

--
Regards

Roger Govier


"Matt D Francis" wrote in
message ...
Hi,

Need help created a calculated field in a Pivot Table.

Below is an example of my Pivot including the first two rows of
data.

Average of LoS DIABETIC
OPCS_1 Diabetic Non Diabetic
A559 13.0 9.1
A578 0.0 0.9


What I want to add for each row is a third column which displays
the
difference between the two columns so I would end up with:

Average of LoS DIABETIC
OPCS_1 Diabetic Non Diabetic DIFFERENCE
A559 13.0 9.1 3.9
A578 0.0 0.9 0.9

I can do this by adding a formula in the cell to the right of the
last
column of the pivot, but this is no good as the references will be
thrown
out if the Pivot changes

So how do I add this calculation as part of the Pivot table so that
it
updates with a refresh?

The field giving the figures for Diabetic & Non Diabetic is an
Average
and
this seems to be causing a problem.