Calculating fields in pivot tables
 

Can anyone help me.

I have used pivot tables a bit, but I am trying to get to grips with
them a bit more and I have hit a stumbling block.

I have a set of data which has two columns, one for reference which is
a unique job number, one for customer where numourous jobs can be for
each customer and a total profit for each job.

The pivot table I have built shows the total profit for each customer
and the number of jobs (using a count on reference) per customer.

What I want to do is enter a calculated field which divides the total
profit for the customer by the number of jobs to give me an average job
profit for each customer.

I have tried =Sum('Gross Profit')/Count('Job Ref') but that just
returns the gross profit by customer number.

Can anyone help?

Thanks

Simon


--
kosciosco
------------------------------------------------------------------------
kosciosco's Profile: http://www.excelforum.com/member.php...fo&userid=7068
View this thread: http://www.excelforum.com/showthread...hreadid=548926

Calculating fields in pivot tables
 

Suggest you do this :

Select your table and creat a pivot table.

Drag customer field to the row field area
Drag the gross profit field to the data field area 3 times.
You will see the data field sum of gross profit 2 and 3.
Right click on these fields and choose field settings
Change the settings in "count of gross profit" for #2 and "average of gross
profit" for # 3. The drag the grey field Data to the total column.

You will now get something that looks like this :




Data
Customer Sum of Gross Profit Count of Gross Profit2 Average of Gross Profit2
Jan 1357 3 452.3333333
Piet 1011 2 505.5
Grand Total 2368 5 473.6


HTH


Hans

"kosciosco" wrote:


Can anyone help me.

I have used pivot tables a bit, but I am trying to get to grips with
them a bit more and I have hit a stumbling block.

I have a set of data which has two columns, one for reference which is
a unique job number, one for customer where numourous jobs can be for
each customer and a total profit for each job.

The pivot table I have built shows the total profit for each customer
and the number of jobs (using a count on reference) per customer.

What I want to do is enter a calculated field which divides the total
profit for the customer by the number of jobs to give me an average job
profit for each customer.

I have tried =Sum('Gross Profit')/Count('Job Ref') but that just
returns the gross profit by customer number.

Can anyone help?

Thanks

Simon


--
kosciosco
------------------------------------------------------------------------
kosciosco's Profile: http://www.excelforum.com/member.php...fo&userid=7068
View this thread: http://www.excelforum.com/showthread...hreadid=548926

Calculating fields in pivot tables
 

Thanks, that worked perfectly


--
kosciosco
------------------------------------------------------------------------
kosciosco's Profile: http://www.excelforum.com/member.php...fo&userid=7068
View this thread: http://www.excelforum.com/showthread...hreadid=548926