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

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
>
>

Thanks, that worked perfectly