Calculated fields in a pivot table

I have a pivot table that displays sums and an average of data relating to staff.

e.g.

Staff Name Average of line rental Sum of total cost 1 Sum of total cost 2 Total of line rental and Cost 2

Bob 12 58 60 ?


I want to add a calculated field into the pivot table so I can add the 'average of line rental' and the 'sum of total cost 2' displayed values together as a new totalised field.

I have tried various formula in the calculated field, but it does not treat the average field as the displayed value 12. Instead it totalises the raw data that has been averaged to produce 12 as a result.

Excel seems to ignore the fact that we already have an average in place.

Is there are way to add the average value 12 to the cost 2 value of 60 within the pivot table?

Thanks in advance.

Craig

File attached now as sample. Please ask if you have any questions.

thanks

Craig

Thank you. I can't work out how to make this work on mass. This was just a small example. I have thousands of rows in my main file with multiple usernames. I don't understand how the $A:$A would deal with that.

Is there anyway to do what I require inside the pivot table after the data has been pivoted?

I have amended the statement =IF(COUNTIF($B$2:$B2,$B2)=1,1*D2+R2,R2)

It adds up ok in the raw data, but doesn't pivot as a total average value.

I will try to create a new example. I was trying to simplify the file for discussion.

I have many rows where the users line rental is pasted and a constant figure so I can average it in the pivot against the username. On every row there are varying values for costs assigned to the user which is why they are summed in the pivot. I need to add the displayed average value in the pivot to the displayed sum value. This makes a new filed of total charge.

If I was doing this outside the pivot the desired result would be achieved by

=--(B5+D5)

Sorry I had missed post #6. I have added the formula, but it takes ages to copy down. I have 200,000 rows plus.

I can see that the formula works on some sample data. As soon as I copy it down my table it grinds my Excel to a halt. I think there is too much for it to do. It is using nearly three GB of memory and 4 processors and it's all flat out. It is taking 15 / 20 mins to update everytime I move around so I have had to remove the formula now.

Yes agreed. Not sure what is going on. Excel corrupted after that and I have had to restart from an earlier file version. As soon as I paste that formula in and copy it down it all goes wrong for me. Anyway not to worry for now. I will try again when I have more time. I will do all this manually for now after the pivot table has been split into separate files.

Thank you so much for all your time.