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